Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional averaging gives wrong result
I've got a problem in a spreadsheet which, if I simplify down to its most
basic is as follows. In the table below, the average value of column B where the corresponding value in column A is "A" should be 100. Yet the answer I get with the following array formula is 33.3333 ={AVERAGE((A1:A3="A")*(G32:G34))} A 100 B 200 C 300 It appears that excel is recognising the correct value for the condition equal to A (100) but then assigning zeros to the other before calculating. Have I got the wrong syntax in that formula. Any suggestions to calculate the correct answer. Tx Rob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional averaging gives wrong result
Try:
=AVERAGE(IF(.........)) ctrl+shift+enter, not just enter "BRob" wrote: I've got a problem in a spreadsheet which, if I simplify down to its most basic is as follows. In the table below, the average value of column B where the corresponding value in column A is "A" should be 100. Yet the answer I get with the following array formula is 33.3333 ={AVERAGE((A1:A3="A")*(G32:G34))} A 100 B 200 C 300 It appears that excel is recognising the correct value for the condition equal to A (100) but then assigning zeros to the other before calculating. Have I got the wrong syntax in that formula. Any suggestions to calculate the correct answer. Tx Rob |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional averaging gives wrong result
As an array formula, committed by pressing Ctrl-Shift-Enter
=average(if(A1:a3="A",G32:G34)) "BRob" wrote: I've got a problem in a spreadsheet which, if I simplify down to its most basic is as follows. In the table below, the average value of column B where the corresponding value in column A is "A" should be 100. Yet the answer I get with the following array formula is 33.3333 ={AVERAGE((A1:A3="A")*(G32:G34))} A 100 B 200 C 300 It appears that excel is recognising the correct value for the condition equal to A (100) but then assigning zeros to the other before calculating. Have I got the wrong syntax in that formula. Any suggestions to calculate the correct answer. Tx Rob |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional averaging gives wrong result
BRob wrote:
I've got a problem in a spreadsheet which, if I simplify down to its most basic is as follows. In the table below, the average value of column B where the corresponding value in column A is "A" should be 100. Yet the answer I get with the following array formula is 33.3333 ={AVERAGE((A1:A3="A")*(G32:G34))} A 100 B 200 C 300 It appears that excel is recognising the correct value for the condition equal to A (100) but then assigning zeros to the other before calculating. Have I got the wrong syntax in that formula. Any suggestions to calculate the correct answer. Tx Rob {=AVERAGE(IF(A1:A3="A",G32:G34,""))} |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
excel result return wrong calcuation result | Excel Worksheet Functions | |||
Need help with averaging a time but dates are messing up the result I am trying for. | Excel Worksheet Functions | |||
sumif wrong result | Excel Worksheet Functions |