Averaging and Rounding problem

[Hansel]

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(H101>0,H101<0,H101=0,H102="N/A")),VALUE(H101),(IF((AND(H102>0,H102<0,H102=0,H101="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!

 

[Biff]

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=H102,H101,AVERAGE(H101:H102)))))

Format as NUMBER 2 decimal places.

Biff

 

[Bernie Deitrick]

Hansel,

Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
last requirement.

HTH,
Bernie
MS Excel MVP

 

[Biff]

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

 

[Ron Rosenfeld]

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(H101>0,H101<0,H101=0,H102="N/A")),VALUE(H101),(IF((AND(H102>0,H102<0,H102=0,H101="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

 

[Bernie Deitrick]

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