Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Averaging and Rounding problem
I would greatly appreciate it if somebody could help me figure out this excel equation. Ok, here are the parameters: I have 2 types of information in two cells labeled as H101 and H102. In the cells there can be positive or negative numbers, 0, or they can read “N/A” which means I have no information for that cell. I am pulling information from both of these cells and putting them into a single cell. Here is what I need: 1) If both cells H101 and H102 have numbers that are the SAME I want the number pulled out and NOT rounded 2) If both cells are N/A I want the N/A pulled out. 3) If one cell is N/A and the other is a number, I want the number pulled out and NOT rounded 4) If both cells are numbers AND they are DIFFERENT I want them AVERAGED and then ROUNDED Here is my problem, I need to average and round a number ONLY when the two cells have numbers in them AND the two numbers are different. If one cell reads 4.65 and the other cell is N/A I want 4.65 pulled out and not rounded. I only want to round numbers to the nearest hundreth when numbers are in both cells AND they are different. Here is the equation I have come up with so far: =IF((AND(H101="N/A",H102="N/A")),"N/A",(IF((AND(H1010,H101<0,H101=0,H102="N/A")),VALUE(H101),(IF((AND(H1020,H102<0,H102=0,H10 1="N/A")),VALUE(H102),(IF(AND((H101=H102)),VALUE(H101), (I F((OR(H101<H202,H101<"N/A",H202<"N/A")), ROUND(AVERAGE(H101,H102), 1)))))))))) This equation keeps rounding all numbers and I only want to round them when both cells contain numbers that are DIFFERENT. If anybody can tell me how I can structure this so that I only round a number when I find the average of them I would greatly appreciate it. Thank you! :-) ps: feel free to change the format of this equation also, I am sure there is a shorter way to do this! -- Hansel ------------------------------------------------------------------------ Hansel's Profile: http://www.excelforum.com/member.php...o&userid=24470 View this thread: http://www.excelforum.com/showthread...hreadid=380726 |
#2
|
|||
|
|||
Hi!
Try this: =IF(OR(H101="",H102=""),"",IF(COUNTIF(H101:H102,"N/A")=2,"N/A",IF(COUNT(H101:H102)=1,MAX(H101:H102),IF(H101=H1 02,H101,AVERAGE(H101:H102))))) Format as NUMBER 2 decimal places. Biff "Hansel" wrote in message ... I would greatly appreciate it if somebody could help me figure out this excel equation. Ok, here are the parameters: I have 2 types of information in two cells labeled as H101 and H102. In the cells there can be positive or negative numbers, 0, or they can read "N/A" which means I have no information for that cell. I am pulling information from both of these cells and putting them into a single cell. Here is what I need: 1) If both cells H101 and H102 have numbers that are the SAME I want the number pulled out and NOT rounded 2) If both cells are N/A I want the N/A pulled out. 3) If one cell is N/A and the other is a number, I want the number pulled out and NOT rounded 4) If both cells are numbers AND they are DIFFERENT I want them AVERAGED and then ROUNDED Here is my problem, I need to average and round a number ONLY when the two cells have numbers in them AND the two numbers are different. If one cell reads 4.65 and the other cell is N/A I want 4.65 pulled out and not rounded. I only want to round numbers to the nearest hundreth when numbers are in both cells AND they are different. Here is the equation I have come up with so far: =IF((AND(H101="N/A",H102="N/A")),"N/A",(IF((AND(H1010,H101<0,H101=0,H102="N/A")),VALUE(H101),(IF((AND(H1020,H102<0,H102=0,H10 1="N/A")),VALUE(H102),(IF(AND((H101=H102)),VALUE(H101), (I F((OR(H101<H202,H101<"N/A",H202<"N/A")), ROUND(AVERAGE(H101,H102), 1)))))))))) This equation keeps rounding all numbers and I only want to round them when both cells contain numbers that are DIFFERENT. If anybody can tell me how I can structure this so that I only round a number when I find the average of them I would greatly appreciate it. Thank you! :-) ps: feel free to change the format of this equation also, I am sure there is a shorter way to do this! -- Hansel ------------------------------------------------------------------------ Hansel's Profile: http://www.excelforum.com/member.php...o&userid=24470 View this thread: http://www.excelforum.com/showthread...hreadid=380726 |
#3
|
|||
|
|||
Hansel,
Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the last requirement. HTH, Bernie MS Excel MVP "Biff" wrote in message ... Hi! Try this: =IF(OR(H101="",H102=""),"",IF(COUNTIF(H101:H102,"N/A")=2,"N/A",IF(COUNT(H101:H102)=1,MAX(H101:H102),IF(H101=H1 02,H101,AVERAGE(H101:H102))))) Format as NUMBER 2 decimal places. Biff |
#4
|
|||
|
|||
I only want to round numbers to the nearest hundreth
=ROUND(AVERAGE(10,15),2) = ??? I'm assuming they want 12.50 vs 12.5 that's why I used the number format. Biff "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Hansel, Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the last requirement. HTH, Bernie MS Excel MVP "Biff" wrote in message ... Hi! Try this: =IF(OR(H101="",H102=""),"",IF(COUNTIF(H101:H102,"N/A")=2,"N/A",IF(COUNT(H101:H102)=1,MAX(H101:H102),IF(H101=H1 02,H101,AVERAGE(H101:H102))))) Format as NUMBER 2 decimal places. Biff |
#5
|
|||
|
|||
On Mon, 20 Jun 2005 18:17:27 -0500, Hansel
wrote: I would greatly appreciate it if somebody could help me figure out this excel equation. Ok, here are the parameters: I have 2 types of information in two cells labeled as H101 and H102. In the cells there can be positive or negative numbers, 0, or they can read “N/A” which means I have no information for that cell. I am pulling information from both of these cells and putting them into a single cell. Here is what I need: 1) If both cells H101 and H102 have numbers that are the SAME I want the number pulled out and NOT rounded 2) If both cells are N/A I want the N/A pulled out. 3) If one cell is N/A and the other is a number, I want the number pulled out and NOT rounded 4) If both cells are numbers AND they are DIFFERENT I want them AVERAGED and then ROUNDED Here is my problem, I need to average and round a number ONLY when the two cells have numbers in them AND the two numbers are different. If one cell reads 4.65 and the other cell is N/A I want 4.65 pulled out and not rounded. I only want to round numbers to the nearest hundreth when numbers are in both cells AND they are different. Here is the equation I have come up with so far: =IF((AND(H101="N/A",H102="N/A")),"N/A",(IF((AND(H1010,H101<0,H101=0,H102="N/A")),VALUE(H101),(IF((AND(H1020,H102<0,H102=0,H10 1="N/A")),VALUE(H102),(IF(AND((H101=H102)),VALUE(H101), (I F((OR(H101<H202,H101<"N/A",H202<"N/A")), ROUND(AVERAGE(H101,H102), 1)))))))))) This equation keeps rounding all numbers and I only want to round them when both cells contain numbers that are DIFFERENT. If anybody can tell me how I can structure this so that I only round a number when I find the average of them I would greatly appreciate it. Thank you! :-) ps: feel free to change the format of this equation also, I am sure there is a shorter way to do this! It looks as if you are entering the N/A as text and it is not the result of a formula. So try this formula: =IF(COUNT(H101:H102)=0,H101, IF(AND(COUNT(H101:H102)=2,H101<H102), ROUND(AVERAGE(H101:H102),2),AVERAGE(H101:H102))) --ron |
#6
|
|||
|
|||
Biff,
Formatting only affects display and not the actual number, so any subsequent calculations would be affected. The OP explicitly requested rounding to two decimal places only if the numbers were different. 1) If both cells H101 and H102 have numbers that are the SAME I want the number pulled out and NOT rounded 2) If both cells are N/A I want the N/A pulled out. 3) If one cell is N/A and the other is a number, I want the number pulled out and NOT rounded 4) If both cells are numbers AND they are DIFFERENT I want them AVERAGED and then ROUNDED Bernie "Biff" wrote in message ... I only want to round numbers to the nearest hundreth =ROUND(AVERAGE(10,15),2) = ??? I'm assuming they want 12.50 vs 12.5 that's why I used the number format. Biff "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Hansel, Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the last requirement. HTH, Bernie MS Excel MVP "Biff" wrote in message ... Hi! Try this: =IF(OR(H101="",H102=""),"",IF(COUNTIF(H101:H102,"N/A")=2,"N/A",IF(COUNT(H101:H102)=1,MAX(H101:H102),IF(H101=H1 02,H101,AVERAGE(H101:H102))))) Format as NUMBER 2 decimal places. Biff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|