ISNUMBER

[Tanya]

Hi
I have this problem:

AF5 has the following formul
=IF(COUNT(Q5:AE5)=0,"-",SUMPRODUCT(--(MOD(COLUMN($P$3:$AD$3)-COLUMN($P$3),2)=0),$P$3:AD$3,Q5:AE5)*0.01)
Resulting in a % or "-"

BF5 has this formul
=IF(COUNT(AQ5:BE5)=0,"-",SUMPRODUCT(--(MOD(COLUMN($AP$3:$BD$3)-COLUMN($AP$3),2)=0),AP$3:$BD$3,AQ5:BE5)*0.01)

NOW...

BM5 needs to decide if AF5 & BF5 have % [may also be 0%] or "-" then if
either of these cells has a % value, I need to devide sum by 2, if not I want
to display "-".

I think I need a combination of IF, ISNUMBER and another operater but can't
get it to work.

Kind Regards
Tanya

 

[Stephen]

Hi
I have this problem:

AF5 has the following formula
=IF(COUNT(Q5:AE5)=0,"-",SUMPRODUCT(--(MOD(COLUMN($P$3:$AD$3)-COLUMN($P$3),2)=0),$P$3:AD$3,Q5:AE5)*0.01)
Resulting in a % or "-"

BF5 has this formula
=IF(COUNT(AQ5:BE5)=0,"-",SUMPRODUCT(--(MOD(COLUMN($AP$3:$BD$3)-COLUMN($AP$3),2)=0),AP$3:$BD$3,AQ5:BE5)*0.01)

NOW...

BM5 needs to decide if AF5 & BF5 have % [may also be 0%] or "-" then if
either of these cells has a % value, I need to devide sum by 2, if not I
want
to display "-".

I think I need a combination of IF, ISNUMBER and another operater but
can't
get it to work.

Kind Regards
Tanya

I'm not quite sure if I understand what you mean by "I need to devide sum by
2". Do you mean you want the sum of AF5 and BF5 divided by 2? If so, try
this:
=IF(OR(ISNUMBER(AF5),ISNUMBER(BF5)),SUM(AF5,BF5)/2,"-")
If not, you may be able to adapt this yourself. Otherwise, post back with
clarification.

 

[RagDyeR]

Try this in BM5:

=IF(SUM(AF5,BF5)/2,SUM(AF5,BF5)/2,"-")

Sum() ignores text!
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi
I have this problem:

AF5 has the following formula
=IF(COUNT(Q5:AE5)=0,"-",SUMPRODUCT(--(MOD(COLUMN($P$3:$AD$3)-COLUMN($P$3),2)=0),$P$3:AD$3,Q5:AE5)*0.01)
Resulting in a % or "-"

BF5 has this formula
=IF(COUNT(AQ5:BE5)=0,"-",SUMPRODUCT(--(MOD(COLUMN($AP$3:$BD$3)-COLUMN($AP$3),2)=0),AP$3:$BD$3,AQ5:BE5)*0.01)

NOW...

BM5 needs to decide if AF5 & BF5 have % [may also be 0%] or "-" then if
either of these cells has a % value, I need to devide sum by 2, if not I
want
to display "-".

I think I need a combination of IF, ISNUMBER and another operater but can't
get it to work.

Kind Regards
Tanya

 

[Tanya]

Thank you Setephen,
Much appreciated.
Kind Regards
Tanya

Hi
I have this problem:

AF5 has the following formula
=IF(COUNT(Q5:AE5)=0,"-",SUMPRODUCT(--(MOD(COLUMN($P$3:$AD$3)-COLUMN($P$3),2)=0),$P$3:AD$3,Q5:AE5)*0.01)
Resulting in a % or "-"

BF5 has this formula
=IF(COUNT(AQ5:BE5)=0,"-",SUMPRODUCT(--(MOD(COLUMN($AP$3:$BD$3)-COLUMN($AP$3),2)=0),AP$3:$BD$3,AQ5:BE5)*0.01)

NOW...

BM5 needs to decide if AF5 & BF5 have % [may also be 0%] or "-" then if
either of these cells has a % value, I need to devide sum by 2, if not I
want
to display "-".

I think I need a combination of IF, ISNUMBER and another operater but
can't
get it to work.

Kind Regards
Tanya

I'm not quite sure if I understand what you mean by "I need to devide sum by
2". Do you mean you want the sum of AF5 and BF5 divided by 2? If so, try
this:
=IF(OR(ISNUMBER(AF5),ISNUMBER(BF5)),SUM(AF5,BF5)/2,"-")
If not, you may be able to adapt this yourself. Otherwise, post back with
clarification.

 

[Tanya]

Thank you RD, whilst your formula worked wonderfully if I had %values >0 and
"-", when the value was "0%" I ended up with "-".
I appreciate your time.
Kind Regards

 

[RagDyeR]

Appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Thank you RD, whilst your formula worked wonderfully if I had %values >0 and
"-", when the value was "0%" I ended up with "-".
I appreciate your time.
Kind Regards