If formula help...
I want a formula in cell S2 to do the following...
If R2 = W and Q2 is a positive number display Q2. If R2 = W and Q2 is a negative number display 100. If R2 = L and Q2 is a positive number display -100. IF R2 = L and Q2 is a negative number display Q2. What formula would I use for this? Thanks. |
Hi
does the following formula give you what you want? =IF(OR(AND(R2="W",Q20),AND(R2="L",Q2<0)),Q2,IF(AN D(R2="W",Q2<0),100,IF(AND(R2="L",Q20),-100,"check entry"))) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Jambruins" wrote in message ... I want a formula in cell S2 to do the following... If R2 = W and Q2 is a positive number display Q2. If R2 = W and Q2 is a negative number display 100. If R2 = L and Q2 is a positive number display -100. IF R2 = L and Q2 is a negative number display Q2. What formula would I use for this? Thanks. |
break it down and then put it together
if(r2="w",if(q2=0,q2,100),if(r2="l",(if(q20,-100,q2))) or another way if(q20,if(r2="w" -- Don Guillett SalesAid Software "Jambruins" wrote in message ... I want a formula in cell S2 to do the following... If R2 = W and Q2 is a positive number display Q2. If R2 = W and Q2 is a negative number display 100. If R2 = L and Q2 is a positive number display -100. IF R2 = L and Q2 is a negative number display Q2. What formula would I use for this? Thanks. |
Try
=IF(AND(R2="w",Q20),Q2,IF(AND(R2="w",Q2<0),100,IF (AND(R2="l",Q20),-100,Q2))) HTH "Jambruins" wrote: I want a formula in cell S2 to do the following... If R2 = W and Q2 is a positive number display Q2. If R2 = W and Q2 is a negative number display 100. If R2 = L and Q2 is a positive number display -100. IF R2 = L and Q2 is a negative number display Q2. What formula would I use for this? Thanks. |
that seems to work, thanks.
"JulieD" wrote: Hi does the following formula give you what you want? =IF(OR(AND(R2="W",Q20),AND(R2="L",Q2<0)),Q2,IF(AN D(R2="W",Q2<0),100,IF(AND(R2="L",Q20),-100,"check entry"))) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Jambruins" wrote in message ... I want a formula in cell S2 to do the following... If R2 = W and Q2 is a positive number display Q2. If R2 = W and Q2 is a negative number display 100. If R2 = L and Q2 is a positive number display -100. IF R2 = L and Q2 is a negative number display Q2. What formula would I use for this? Thanks. |
try
=if(r2="W",if(Q20,Q2,100),if(Q20,-100,Q2)) you may need to play with Q20 to be Q2=0 If R2 can be something other than W or L you may have to add to the equation "Jambruins" wrote: I want a formula in cell S2 to do the following... If R2 = W and Q2 is a positive number display Q2. If R2 = W and Q2 is a negative number display 100. If R2 = L and Q2 is a positive number display -100. IF R2 = L and Q2 is a negative number display Q2. What formula would I use for this? Thanks. |
All times are GMT +1. The time now is 03:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com