|
ISNUMBER
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 |
ISNUMBER
"Tanya" wrote in message
... 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. |
ISNUMBER
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! ================================================== === "Tanya" wrote in message ... 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 |
ISNUMBER
Thank you Setephen,
Much appreciated. Kind Regards Tanya "Stephen" wrote: "Tanya" wrote in message ... 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. |
ISNUMBER
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" wrote: 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! ================================================== === "Tanya" wrote in message ... 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 |
ISNUMBER
Appreciate the feed-back.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Tanya" wrote in message ... 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" wrote: 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! ================================================== === "Tanya" wrote in message ... 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 |
| All times are GMT +1. The time now is 01:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com