Equation setup 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, 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), (IF((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=380710

I think this should work for you. There is a lot of logic in here, but
I think I've captured it all. I found that you were redundant in some
of the logic. Also, the biggest change is the use of ISERROR instead
of ="NA" in your IF statements - I'm assuming you are testing for true
errors here instead of the literal text NA. This is why all of your
formulas were defaulting to the final arguement. Let me know if it
doesn't work. Later, Chad


=IF(AND(ISERROR(H101),ISERROR(H102)),"N/A",IF(ISERROR(H102),VALUE(H101),IF(ISERROR(H101),V ALUE(H102),IF(H101=H102,VALUE(H101),ROUND(AVERAGE( H101,H102),
1)))))


--
cvolkert
------------------------------------------------------------------------
cvolkert's Profile: http://www.excelforum.com/member.php...o&userid=24380
View this thread: http://www.excelforum.com/showthread...hreadid=380710

See another possible solution in your other post.

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, 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), (IF((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=380710