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
|