Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging data and rounding up
I am currently using the following formula as this averages up my data to
either 0.1, 0.5 or 0.9 (I hope!): =INT(AVERAGE(P2,S2,V2))+CHOOSE((MOD(AVERAGE(P2,S2, V2),1)<0.3)+(MOD(AVERAGE(P2,S2,V2),1)<0.7)+1,0.9,0 .5,0.1) If the cells I am averaging do not contain data then the formula does not make a calculation. Is there a way the formula can still calculate the average even if a cell does not contain data? Many thanks for your help John |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging data and rounding up
Instead of using the AVERAGE() function, do it manually in each section of
the formula where you're using it now: AVERAGE(P2,S2,V2) becomes ((P2+S2+V2)/3) That will treat empty cells as zero. It may or may give you the results you want. Given P2 = 1, S2 = 2, V2 = 3 then both formulas will return 2 (6/3) But Given P2=1 S2 empty, V2 = 3 then AVERAGE() will give 2 (4/2) while the manual method would give 1.33333 (4/3). "John Mac" wrote: I am currently using the following formula as this averages up my data to either 0.1, 0.5 or 0.9 (I hope!): =INT(AVERAGE(P2,S2,V2))+CHOOSE((MOD(AVERAGE(P2,S2, V2),1)<0.3)+(MOD(AVERAGE(P2,S2,V2),1)<0.7)+1,0.9,0 .5,0.1) If the cells I am averaging do not contain data then the formula does not make a calculation. Is there a way the formula can still calculate the average even if a cell does not contain data? Many thanks for your help John |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging data and rounding up
It seems that my formula:
=INT(AVERAGE(P2,S2,V2))+CHOOSE((MOD(AVERAGE(P2,S2, V2),1)<0.3)+(MOD(AVERAGE(P2,S2,V2),1)<0.7)+1,0.9,0 .5,0.1) will not calculate the average of the cells if one of the cells is blank, is there any way of getting around this? Also has does this formula round of the answer, does it use the nearest to 0.1, 0.5, 0.9 or does it round up /down? "JLatham" wrote: Instead of using the AVERAGE() function, do it manually in each section of the formula where you're using it now: AVERAGE(P2,S2,V2) becomes ((P2+S2+V2)/3) That will treat empty cells as zero. It may or may give you the results you want. Given P2 = 1, S2 = 2, V2 = 3 then both formulas will return 2 (6/3) But Given P2=1 S2 empty, V2 = 3 then AVERAGE() will give 2 (4/2) while the manual method would give 1.33333 (4/3). "John Mac" wrote: I am currently using the following formula as this averages up my data to either 0.1, 0.5 or 0.9 (I hope!): =INT(AVERAGE(P2,S2,V2))+CHOOSE((MOD(AVERAGE(P2,S2, V2),1)<0.3)+(MOD(AVERAGE(P2,S2,V2),1)<0.7)+1,0.9,0 .5,0.1) If the cells I am averaging do not contain data then the formula does not make a calculation. Is there a way the formula can still calculate the average even if a cell does not contain data? Many thanks for your help John |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Averaging data and rounding up
John,
I gave you answer on how to make sure the average is an average in my first reply. You can test how it is working, rounding/returning results by 'observation': place some values in P2, S3 and V2 and observe the value returned. If it is not as you require, then we can examine it closer and determine if we need to change anything else. You could break the formula out into separate cells to see what values each section returns, as: =MOD((P2+S2+V2)/3,1) or =MOD((P2+S2+V2)/3,1) < 0.3 (and I'm thinking, without testing) that is going to return TRUE or FALSE, although it may return 1 or 0 - again, testing would reveal the answer. to see what that formula returns and how it compares to your desired/expected results. or even =MOD((P2+S2+V2)/3,1) + MOD((P2+S2+V2)/3,1) + 1 "John Mac" wrote: It seems that my formula: =INT(AVERAGE(P2,S2,V2))+CHOOSE((MOD(AVERAGE(P2,S2, V2),1)<0.3)+(MOD(AVERAGE(P2,S2,V2),1)<0.7)+1,0.9,0 .5,0.1) will not calculate the average of the cells if one of the cells is blank, is there any way of getting around this? Also has does this formula round of the answer, does it use the nearest to 0.1, 0.5, 0.9 or does it round up /down? "JLatham" wrote: Instead of using the AVERAGE() function, do it manually in each section of the formula where you're using it now: AVERAGE(P2,S2,V2) becomes ((P2+S2+V2)/3) That will treat empty cells as zero. It may or may give you the results you want. Given P2 = 1, S2 = 2, V2 = 3 then both formulas will return 2 (6/3) But Given P2=1 S2 empty, V2 = 3 then AVERAGE() will give 2 (4/2) while the manual method would give 1.33333 (4/3). "John Mac" wrote: I am currently using the following formula as this averages up my data to either 0.1, 0.5 or 0.9 (I hope!): =INT(AVERAGE(P2,S2,V2))+CHOOSE((MOD(AVERAGE(P2,S2, V2),1)<0.3)+(MOD(AVERAGE(P2,S2,V2),1)<0.7)+1,0.9,0 .5,0.1) If the cells I am averaging do not contain data then the formula does not make a calculation. Is there a way the formula can still calculate the average even if a cell does not contain data? Many thanks for your help John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
averaging data | Excel Discussion (Misc queries) | |||
Averaging data | Excel Worksheet Functions | |||
Averaging/Rounding Equation Problem | Excel Worksheet Functions | |||
Averaging/Rounding Equation problem | Excel Discussion (Misc queries) | |||
Averaging and Rounding problem | Excel Worksheet Functions |