Averaging/Rounding Equation Problem
 

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
thank you 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),AVERAG E(H101:H10 2)))

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! :)


--
Hansel
------------------------------------------------------------------------
Hansel's Profile: http://www.excelforum.com/member.php...o&userid=24470
View this thread: http://www.excelforum.com/showthread...hreadid=382881

This will pass a 1 or 2 to the round function based on the average value:

=IF(COUNT(H101:H102)=0,H101,IF(AND(COUNT(H101:H102 )=2,H101<H102),
ROUND(AVERAGE(H101:H102),IF(INT(MOD(ROUND(AVERAGE( H101:H102),2),1)
/0.25)=(MOD(ROUND(AVERAGE(H101:H102),2),1)/0.25),2,1)),AVERAGE(H101:H102)))

HTH,
Bernie
MS Excel MVP


"Hansel" wrote in message
...

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
thank you 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),AVERAG E(H101:H10 2)))

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! :)


--
Hansel
------------------------------------------------------------------------
Hansel's Profile: http://www.excelforum.com/member.php...o&userid=24470
View this thread: http://www.excelforum.com/showthread...hreadid=382881

try something like
=if(H101="",if(H102="","",H102),if(H102="",H101,if (H101=H102,H101,if(mod(H101+H102,1)=.5,round((H101 +H102)/2,2),round((H101+H102)/2,1)))))
The mod portion may need to be changed depending on what significant figures
go into the H101 and H102
mod(round(H101+H102,1),1)
"Hansel" wrote:


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
thank you 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),AVERAG E(H101:H10 2)))

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! :)


--
Hansel
------------------------------------------------------------------------
Hansel's Profile: http://www.excelforum.com/member.php...o&userid=24470
View this thread: http://www.excelforum.com/showthread...hreadid=382881

Thank you for your reply! I think this may have done the trick. It is
greatly appreciated! :)


--
Hansel
------------------------------------------------------------------------
Hansel's Profile: http://www.excelforum.com/member.php...o&userid=24470
View this thread: http://www.excelforum.com/showthread...hreadid=382881