Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone tell me what is wrong with this formula please?!
=SUMIF('Hourly Conversion'!$C:$C,Calculations!$A:$A,(AVERAGE('Hou rly
Conversion'!$L:$L))) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone tell me what is wrong with this formula please?!
What are you trying to do? You seem to want an average but the criteria is
unclear. This is how SUMIF works =SUMIF(range1,test,range2) It compares each element in range1 to the test and then adds the corresponding cells in range2 (or in range1 if the third argument is omitted) Your formula looks at the data in 'Hourly Conversion'!$C:$C It should compare each of these to a single value; your formula has an array Calculations!$A:$A Your last argument is not a range but a function How about SUMIF(range1,test,range2)/COUNTIF(range1,test) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "kippers" wrote in message ... =SUMIF('Hourly Conversion'!$C:$C,Calculations!$A:$A,(AVERAGE('Hou rly Conversion'!$L:$L))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone tell me what is wrong with this formula please?!
Bernard,
From what you have said I think SUMIF may not be the right function. I am trying to get an average of all cells in 'Hourly Conversion' that match the same criteria as those cells in 'Calculations' (April) i.e. The following data is from 'Hourly Conversion' and I wish to take an average of all the times recorded in April and present the result in the Calculations worksheet. Hourly Conversion April 00:45:02 May 00:00:00 April 00:10:02 January 00:00:02 November 01:00:02 April 00:00:35 May 01:45:52 January 00:45:49 May 00:19:41 Cheers, "Bernard Liengme" wrote: What are you trying to do? You seem to want an average but the criteria is unclear. This is how SUMIF works =SUMIF(range1,test,range2) It compares each element in range1 to the test and then adds the corresponding cells in range2 (or in range1 if the third argument is omitted) Your formula looks at the data in 'Hourly Conversion'!$C:$C It should compare each of these to a single value; your formula has an array Calculations!$A:$A Your last argument is not a range but a function How about SUMIF(range1,test,range2)/COUNTIF(range1,test) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "kippers" wrote in message ... =SUMIF('Hourly Conversion'!$C:$C,Calculations!$A:$A,(AVERAGE('Hou rly Conversion'!$L:$L))) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can anyone tell me what is wrong with this formula please?!
Assuming the month names are in column A and the times in B, then
=SUMIF('Hourly Conversion'!$A:$A,"April", 'Hourly Conversion'!$B:$B) will SUM all the times for B cells whose corresponding A cell has April If your Calculations worksheet has the word April in cell A1 then =SUMIF('Hourly Conversion'!$A:$A, A1, 'Hourly Conversion'!$B:$B) will do the same Likewise =COUNTIF('Hourly Conversion'!$A:$A,"April", 'Hourly Conversion'!$B:$B) or =COUNTIF('Hourly Conversion'!$A:$A, A1, 'Hourly Conversion'!$B:$B) will COUNT these cells. And SUMIF()/COUNTIF() will give the average best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "kippers" wrote in message ... Bernard, From what you have said I think SUMIF may not be the right function. I am trying to get an average of all cells in 'Hourly Conversion' that match the same criteria as those cells in 'Calculations' (April) i.e. The following data is from 'Hourly Conversion' and I wish to take an average of all the times recorded in April and present the result in the Calculations worksheet. Hourly Conversion April 00:45:02 May 00:00:00 April 00:10:02 January 00:00:02 November 01:00:02 April 00:00:35 May 01:45:52 January 00:45:49 May 00:19:41 Cheers, "Bernard Liengme" wrote: What are you trying to do? You seem to want an average but the criteria is unclear. This is how SUMIF works =SUMIF(range1,test,range2) It compares each element in range1 to the test and then adds the corresponding cells in range2 (or in range1 if the third argument is omitted) Your formula looks at the data in 'Hourly Conversion'!$C:$C It should compare each of these to a single value; your formula has an array Calculations!$A:$A Your last argument is not a range but a function How about SUMIF(range1,test,range2)/COUNTIF(range1,test) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "kippers" wrote in message ... =SUMIF('Hourly Conversion'!$C:$C,Calculations!$A:$A,(AVERAGE('Hou rly Conversion'!$L:$L))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) | |||
excel formula calculations are wrong | Excel Worksheet Functions | |||
Formula retrieves wrong data | Excel Discussion (Misc queries) | |||
Paste is is copying in formula, but display is wrong. | Excel Discussion (Misc queries) |