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

"Hansel" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> Hansel
> ------------------------------------------------------------------------
> Hansel's Profile:
> http://www.excelforum.com/member.php...o&userid=24470
> View this thread: http://www.excelforum.com/showthread...hreadid=380726
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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

[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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Hansel,
>
> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
> last requirement.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

[Ron Rosenfeld]

On Mon, 20 Jun 2005 18:17:27 -0500, Hansel
<[email protected]> 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(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


"Biff" <[email protected]> wrote in message
news:[email protected]...
> >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
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> Hansel,
>>
>> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
>> last requirement.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>
>>
>
>

[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

"Hansel" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> Hansel
> ------------------------------------------------------------------------
> Hansel's Profile:
> http://www.excelforum.com/member.php...o&userid=24470
> View this thread: http://www.excelforum.com/showthread...hreadid=380726
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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

[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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Hansel,
>
> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
> last requirement.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

[Ron Rosenfeld]

On Mon, 20 Jun 2005 18:17:27 -0500, Hansel
<[email protected]> 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(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


"Biff" <[email protected]> wrote in message
news:[email protected]...
> >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
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> Hansel,
>>
>> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
>> last requirement.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>
>>
>
>

[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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Hansel,
>
> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
> last requirement.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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]

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


"Biff" <[email protected]> wrote in message
news:[email protected]...
> >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
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> Hansel,
>>
>> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
>> last requirement.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>
>>
>
>

[Ron Rosenfeld]

On Mon, 20 Jun 2005 18:17:27 -0500, Hansel
<[email protected]> 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(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]

Hansel,

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

HTH,
Bernie
MS Excel MVP



"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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

[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

"Hansel" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> Hansel
> ------------------------------------------------------------------------
> Hansel's Profile:
> http://www.excelforum.com/member.php...o&userid=24470
> View this thread: http://www.excelforum.com/showthread...hreadid=380726
>

[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


"Biff" <[email protected]> wrote in message
news:[email protected]...
> >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
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> Hansel,
>>
>> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
>> last requirement.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>
>>
>
>

[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

"Hansel" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> Hansel
> ------------------------------------------------------------------------
> Hansel's Profile:
> http://www.excelforum.com/member.php...o&userid=24470
> View this thread: http://www.excelforum.com/showthread...hreadid=380726
>

[Ron Rosenfeld]

On Mon, 20 Jun 2005 18:17:27 -0500, Hansel
<[email protected]> 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(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]

Hansel,

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

HTH,
Bernie
MS Excel MVP



"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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

[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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Hansel,
>
> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
> last requirement.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

[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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Hansel,
>
> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
> last requirement.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

[Ron Rosenfeld]

On Mon, 20 Jun 2005 18:17:27 -0500, Hansel
<[email protected]> 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(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]

Hansel,

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

HTH,
Bernie
MS Excel MVP



"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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]

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


"Biff" <[email protected]> wrote in message
news:[email protected]...
> >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
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> Hansel,
>>
>> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
>> last requirement.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>
>>
>
>

[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

"Hansel" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> Hansel
> ------------------------------------------------------------------------
> Hansel's Profile:
> http://www.excelforum.com/member.php...o&userid=24470
> View this thread: http://www.excelforum.com/showthread...hreadid=380726
>

[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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Hansel,
>
> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
> last requirement.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

[Ron Rosenfeld]

On Mon, 20 Jun 2005 18:17:27 -0500, Hansel
<[email protected]> 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(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]

Hansel,

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

HTH,
Bernie
MS Excel MVP



"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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]

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


"Biff" <[email protected]> wrote in message
news:[email protected]...
> >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
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> Hansel,
>>
>> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
>> last requirement.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>
>>
>
>

[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

"Hansel" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> Hansel
> ------------------------------------------------------------------------
> Hansel's Profile:
> http://www.excelforum.com/member.php...o&userid=24470
> View this thread: http://www.excelforum.com/showthread...hreadid=380726
>

[Ron Rosenfeld]

On Mon, 20 Jun 2005 18:17:27 -0500, Hansel
<[email protected]> 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(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

[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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Hansel,
>
> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
> last requirement.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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]

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


"Biff" <[email protected]> wrote in message
news:[email protected]...
> >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
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> Hansel,
>>
>> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
>> last requirement.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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

[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

"Hansel" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> Hansel
> ------------------------------------------------------------------------
> Hansel's Profile:
> http://www.excelforum.com/member.php...o&userid=24470
> View this thread: http://www.excelforum.com/showthread...hreadid=380726
>

[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

"Hansel" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> Hansel
> ------------------------------------------------------------------------
> Hansel's Profile:
> http://www.excelforum.com/member.php...o&userid=24470
> View this thread: http://www.excelforum.com/showthread...hreadid=380726
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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

[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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Hansel,
>
> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
> last requirement.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

[Ron Rosenfeld]

On Mon, 20 Jun 2005 18:17:27 -0500, Hansel
<[email protected]> 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(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


"Biff" <[email protected]> wrote in message
news:[email protected]...
> >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
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> Hansel,
>>
>> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
>> last requirement.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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]

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


"Biff" <[email protected]> wrote in message
news:[email protected]...
> >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
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> Hansel,
>>
>> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
>> last requirement.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>
>>
>
>

[Ron Rosenfeld]

On Mon, 20 Jun 2005 18:17:27 -0500, Hansel
<[email protected]> 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(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

[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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Hansel,
>
> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
> last requirement.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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

[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

"Hansel" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> Hansel
> ------------------------------------------------------------------------
> Hansel's Profile:
> http://www.excelforum.com/member.php...o&userid=24470
> View this thread: http://www.excelforum.com/showthread...hreadid=380726
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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]

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


"Biff" <[email protected]> wrote in message
news:[email protected]...
> >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
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> Hansel,
>>
>> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
>> last requirement.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>
>>
>
>

[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

"Hansel" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> Hansel
> ------------------------------------------------------------------------
> Hansel's Profile:
> http://www.excelforum.com/member.php...o&userid=24470
> View this thread: http://www.excelforum.com/showthread...hreadid=380726
>

[Ron Rosenfeld]

On Mon, 20 Jun 2005 18:17:27 -0500, Hansel
<[email protected]> 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(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

[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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Hansel,
>
> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
> last requirement.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

[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

"Hansel" <[email protected]> wrote in
message news:[email protected]...
>
> 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!
>
>
> --
> Hansel
> ------------------------------------------------------------------------
> Hansel's Profile:
> http://www.excelforum.com/member.php...o&userid=24470
> View this thread: http://www.excelforum.com/showthread...hreadid=380726
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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

[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

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> Hansel,
>
> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
> last requirement.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

[Ron Rosenfeld]

On Mon, 20 Jun 2005 18:17:27 -0500, Hansel
<[email protected]> 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(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


"Biff" <[email protected]> wrote in message
news:[email protected]...
> >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
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
>> Hansel,
>>
>> Change the AVERAGE(H101:H102) to ROUND(AVERAGE(H101:H102),2) to meet the
>> last requirement.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>
>>
>
>