Averaging/Rounding Equation problem

[Hansel]

I would greatly appreciate it if somebody could help me figure out the
final piece to this excel equation. I posted about this before, so
thankyou to those who helped me out... =)

Here are the parameters for my equation:

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. However, I do NOT want to round the average
if the averaged number contains a .25 or a .75. For example, if I
average 2.00 and 2.50, I want Excel to give me a 2.25 and NOT round
this to 2.30.

Here is the equation I have so far:

=IF(COUNT(H101:H102)=0,H101,IF(AND(COUNT(H101:H102)=2,H101<>H102),ROUND(AVERAGE(H101:H102),1),AVERAGE(H101:H102)))

This equation is almost done, I just need to add an extra instruction
that will NOT round an averaged number that contains a .25 or .75. I am
calculating averages of oil prices so they have to be as exact as
possible. Thank you in advance for your help!

 

[joeu2004]

4) If both cells are numbers AND they are DIFFERENT I want them

AVERAGED and then ROUNDED. However, I do NOT want to round the average
if the averaged number contains a .25 or a .75. For example, if I
average 2.00 and 2.50, I want Excel to give me a 2.25 and NOT round
this to 2.30.

It sounds like you want MROUND(). For example:
MROUND(AVERAGE(H101:H102),0.25) will round to
multiples of 0.25.

 

[Guest]

Hi Hansel, With your entries in H101 and H102, use the following formulae in
H103 or where it suits you:

=IF(OR(H101="",H102=""0,"",IF(AND(ISNA(H101),ISNA(H102)),H101,IF(ISNA(H101),H102,IF(ISNA(H102),H101,IF(H101=H102,H101,IF(H101<>H102,AVERAGE(H101:H102)))))))

and in H104:

=IF(H103="","",IF(ISNA(H103),H103,IF(OR(ISNA(H101),ISNA(H102)),H103,IF(H101<>H102,H103,IF(H101=H102,H103,IF(OR(RIGHT(H103,2)=25,RIGHT(H103,2)=75,H103,ROUND(H103,2)))))))

 

[JE McGimpsey]

One way:

=IF(COUNT(H101:H102)=0,H101,ROUND(AVERAGE(H101:H102),1) -
0.05*(MOD(AVERAGE(H101:H102)*4,2)=1)

 

[Ron Rosenfeld]

This equation is almost done, I just need to add an extra instruction
that will NOT round an averaged number that contains a .25 or .75. I am
calculating averages of oil prices so they have to be as exact as
possible. Thank you in advance for your help!

I assume you mean "ends with" rather than "contains".

But what if the averaged number ends with .44?

Do you, perhaps, want to do your rounding to the nearest 0.25?

If so:

=IF(COUNT(H101:H102)=0,H101,
IF(AND(COUNT(H101:H102)=2,H101<>H102),
ROUND(AVERAGE(H101:H102)*4,0)/4,AVERAGE(H101:H102)))


--ron