|
More site info...
Excel Worksheet Functions | Forum profile
|
|
Forum profile page for Excel Worksheet Functions on http://www.excelforum.com.
This report page is the aggregated overview from a single forum: Excel Worksheet Functions, located on the Message Board at http://www.excelforum.com.
This forum profile page summarizes the general forum statistics such as: Users Activity, Forum Activity, and Top Authors, which are reported in either a table or graph below for a given reporting time period.
Additional forum profile information for "Excel Worksheet Functions" on the Message Board at http://www.excelforum.com is also shown in the following ways:
1) Latest Active Threads
2) Hot Threads for Last Week
Warning: These statistics are generated using 'best efforts' and can experience delays and reporting errors at times. Please note that such statistics do not constitute a forum's popularity and/or exact posting volumes at any given reporting period.
|
|
|
|
|
Posting activity on Excel Worksheet Functions:
|
|
Week
|
Month
|
3 Months
|
|
Threads:
|
107
|
441
|
1,215
|
|
Post:
|
386
|
1,549
|
4,201
|
|
|
Excel Worksheet Functions Posting activity graph:
|
Top authors during last week:
user's latest post:
COUNT using IF Statment Broken?
Published (2009-11-27 14:46:00)
Quote: Originally Posted by shg You can omit the average_range since it's the same as the criteria range: =AVERAGEIF(D:D, ">0") Didn't think of that - thanks.
user's latest post:
Finding Average
Published (2009-11-27 10:47:00)
maybe look into AVERAGEIF() ?
user's latest post:
Auto Sort results
Published (2009-11-22 22:11:00)
Please Register to Remove these Ads Hi, I'm trying to do an auto sort whenever shortlisted admin worksheet is updated. When updates are made to Cell B, Cell Z will sort by descending. But the following macro does not sort the list whenever new data are added to cell B. Following are the codes, i not sure where i did wrong. B11:Z11 is the header I have added this piece of macro to the worksheet(Shortlisted Admin) that needs to be sorted....
user's latest post:
[SOLVED] Array Formulas instead...
Published (2009-11-25 15:52:00)
Please Register to Remove these Ads Quote: Originally Posted by ElmerS Thank you, Andy, It seems as if the only task Excel is not capable of is preparing a cup of coffee. Elm Place I pot of water on your computer case, let Excel run sum massive Array formulas for a while, and watch the pot boil... just add instant coffee granules.... now Excel made your coffee
user's latest post:
How to set-up a Group Box with...
Published (2009-11-26 15:40:00)
Dear Forum, Is there anyone who can pleas e help or guide in this path of Option Buttons as Im not able to follow head or tail from the info provided on various sites. Please can someone atleast show how to just set-up the Group for Option Buttons?
ChalkerL ChalkerL is offline...
9
user's latest post:
Delete of duplicate records
Published (2009-11-22 16:19:00)
Can i incorporate your code into my formula Code: =IF(jobsApplicationdb!G36='Person Specification'!$F$7,IF(FIND(jobsApplicationdb!I36,'Person Specification'!$H$7),IF(FIND(jobsApplicationdb!H36,'Person Specification'!$G$7),IF(ISTEXT(jobsApplicationdb!J36),IF(AND(jobsApplicationdb!P36='Person Specification'!$N$7,jobsApplicationdb!Q36='Person Specification'!$O$7,jobsApplicationdb!R36='Person...
user's latest post:
Array Formulas instead of helper...
Published (2009-11-25 15:48:00)
Thank you, Andy, It seems as if the only task Excel is not capable of is preparing a cup of coffee. Elm
user's latest post:
Count aging records
Published (2009-11-25 10:14:00)
Thanks NBVC. I was trying the same formula without the quotation marks and the ambersand to no avail. The addition of those worked perfectly.
user's latest post:
COUNT using IF Statment Broken?
Published (2009-11-27 14:37:00)
You can omit the average_range since it's the same as the criteria range: =AVERAGEIF(D:D, ">0") To get the same result pre-2007 if col D includes negative numbers, =SUMIF(D:D, ">0") / COUNTIF(D:D, ">0")
user's latest post:
Adding extra if statements
Published (2009-11-21 15:45:00)
Hi, It's not clear entirely what the purpose of this big IF statement is. It just seems to be returning the NRIC number. Why not just list the names in column C of the Shorlisted.. sheet rather than a VLOOKUP and then useSurely you can just use Code: =INDEX(jobsApplicationdb!C:C,MATCH(C10,jobsApplicationdb!D:D,FALSE),1) in B10 and copied down. Incidentally there seems to be an inconsistency between the current B10 & B11 cells....
|
|
|
|
Latest active threads on Excel Worksheet Functions::
Started 1 day, 16 hours ago (2009-11-27 14:08:00)
by DonkeyOte
Re: point 1
If you're using XL2007 onwards (2012... really ?!) then use AVERAGEIF
=AVERAGEIF(D:D,">0",D:D)
Pre 2007
=SUM(D:D)/COUNTIF(D:D,">0")
As for your other question... one question per thread please (if unrelated which is the case here).
Started 1 day, 17 hours ago (2009-11-27 12:59:00)
by DonkeyOte
Pre XL2007 you would really be obliged to look at SUMPRODUCT but in truth the "lame" approach you've adopted is the best as it's the most efficient - odd though that may seem.
SUMPRODUCT is not an efficient formula whereas COUNTIF etc is...
I would personally advise you persevere with the method you have adopted already though you could reduce the number of helpers you have by using ...
Started 1 day, 20 hours ago (2009-11-27 10:47:00)
by teylyn
maybe look into AVERAGEIF() ?
Started 1 day, 21 hours ago (2009-11-27 08:57:00)
by rwgrietveld
Give us an eample Before and After and tell us where the database is (where the data is coming from)
Started 1 day, 22 hours ago (2009-11-27 08:42:00)
by rwgrietveld
DonkeyOte and I just finished a similar thread http://www.excelforum.com/excel-misc...ata-range.h tml
Started 2 days, 14 hours ago (2009-11-26 16:29:00)
by darkyam
=A2-A1+1 should work.
That takes the difference in days inclusive of both start and end dates.
Started 2 weeks, 1 day ago (2009-11-13 17:26:00)
by daddylonglegs
You can't use SUMIF because the sum range must always be the same size and shape as the criteria range, so if you use
=SUMIF(A1:A4;"a";A6:D6)
That formula actually uses a sum range of A6:A9...
You can't use TRANSPOSE within SUMIF because the result of TRANSPOSE is an array, SUMIF doesn't accept an array as the sum range (it must be a range).
You can use TRANSPOSE in an array ...
Started 2 days, 4 hours ago (2009-11-27 02:14:00)
by Paul
I can see why you wouldn't want to use data validation to hardcode each cell, however you can use the same validation formula for all cells in the column.
Select A1:A n then open the Data Validation dialog. Choose 'Custom' and use the following formula:
=A1=ROW(A1)
All of those cells will only allow a value equal to the row in which it is entered (Row 1 must = 1, row 2 must = 2,...
Started 2 days, 2 hours ago (2009-11-27 04:02:00)
by Paul
Hi Blleow,
The quickest method to do this would be with a pivot table. Select your data and then click Insert -> Pivot Table.
Once the pivot table is on-screen, simply drag the "Edition" field to the Row Headers section, and drag the "Company" field to the Values section. That will give you a count of Companies per Edition.
Hope that helps!
|
|
Hot threads for last week on Excel Worksheet Functions::
Started 6 days, 19 hours ago (2009-11-22 11:23:00)
by ChalkerL ChalkerL is offline Registered User
Please Register to Remove these Ads
Hey guys,
I'm having trouble getting this piece of macro to work. I want to delete duplicate Id records in (Cell B12 : B124), but only those with "No" in the (Cell Z12 : Z124) will be deleted.
Table
B12 Z12
-------------
E1 Yes
E2 Yes
E3 Yes
E4 Yes
E1 No
E2 No
E3 No...
Started 1 week ago (2009-11-21 12:40:00)
by JeanRage
Hi,
A possibility is to code with VBA the creation of your named ranges with your rules ...
thus getting rid of the helper columns ...
HTH
Started 1 week, 1 day ago (2009-11-20 14:37:00)
by acsishere acsishere is offline Valued Forum Contributor
Please Register to Remove these Ads
Dear Friends,
I am using EXCEL 2003. I am to find Average using few conditions. I have explained the requirement in the sample workbook.
Please give me a solution.
Thanks in advance.
acsishere.
Started 5 days ago (2009-11-24 06:13:00)
by DonkeyOte
Started 3 days, 19 hours ago (2009-11-25 11:14:00)
by cmar6
I have a column of numbers, each from 6 to 10 digits (100 thousands to billions).
I want another column next to it in which the numbers will be round to millions with 1 decimal, e.g., -657,960 would be rounded to -.7 and -1,640,837,974 would be rounded to -1640.8
I have played around with "mround" and "round" but to no avail.
I wonder if anyone has an idea...
Started 1 week, 1 day ago (2009-11-20 09:36:00)
by DonkeyOte
I would suggest posting a sample file - your layout is not very clear.
If product is in A, 2008 is B, 2009 C etc... then to SUM T-Shirt revenue for 2008
=SUMIF(A:A,"T-Shirt",B:B)
Started 5 days, 12 hours ago (2009-11-23 18:55:00)
by teylyn
what do you mean by minimum time? The first time they logged in? The shortest duration they were logged in for? Maybe provide a small data sample with the desired results ...
You can upload a file by clicking "Go Advanced" below and then the paper clip icon.
Started 3 days, 20 hours ago (2009-11-25 09:58:00)
by scaffdog845 Registered User
morning all,
I run a work order report on a daily and weekly basis and I am looking for a formula that will find all dates in L:L that are greater than 9 days old. I can't seem to work out the syntax. The cells in L:L are formatted as DATE and TYPE *3/14/2001
As always the help is appreciated.
Started 6 days, 12 hours ago (2009-11-22 18:20:00)
by JBeaucaire
One way would be to add a macro that can insert formulas into each row based on the the correct sheet name reference, however you want the macro to determine that. The main goal is to have normal direct formulas in the cells.
Started 1 week ago (2009-11-21 17:10:00)
by davidmarlow
Apologies - ignore 1st file uploaded and see attached.....
This is also a growing table (i.e. can be adding extra columns and rows) so any solution needs to be flexible and easy to update.....
Thanks again
|
|
Size: 397 bytes
