IFs and ORs

  • Thread starter Thread starter DavidT
  • Start date Start date
D

DavidT

Tried every combination I could think of, here's what I need for for a
solution for ONE cell:

If D1=2 and D3 is <14 then D5=0 , If D1=2 and D3 is >14 then D3-14*F1 OR
If D1=3 and D3 is <16 then D5=0 , If D1=3 and D3 is >16 then D3-16*F1 OR
If D1=4 and D3 is <17 then D5=0 , If D1=4 and D3 is >17 then D3-17*F1

How can I get this into a single formula for an answer in one cell?
 
Try in D
=IF(AND(D1=2,D3<14),0,IF(AND(D1=2,D3>=14),D3-14*F1,IF(AND(D1=3,D3<16),0,IF(AND(D1=3,D3>=16),D3-16*F1,IF(AND(D1=4,D3<17),0,IF(AND(D1=4,D3>=17),D3-17*F1,"ERROR"))))))

I am testing it ... You do too.
 
=IF(AND(D1=2,D3<14),0,IF(AND(D1=2,D3>=14),D3-14*F1,IF(AND(D1=3,D3<16),0,IF(AND(D1=3,D3>=16),D3-16*F1,IF(AND(D1=4,D3<17),0,IF(AND(D1=4,D3>=17),D3-17*F1,"ERROR"))))))

Seems to be Ok.
Note that I have added equality condition also...
 
Tried every combination I could think of, here's what I need for for a
solution for ONE cell:

If D1=2 and D3 is <14 then D5=0 , If D1=2 and D3 is >14 then D3-14*F1 OR
If D1=3 and D3 is <16 then D5=0 , If D1=3 and D3 is >16 then D3-16*F1 OR
If D1=4 and D3 is <17 then D5=0 , If D1=4 and D3 is >17 then D3-17*F1

How can I get this into a single formula for an answer in one cell?
Click to expand...


You don't define what you want as a result should D1 not equal 2, 3 or 4; nor
do you define what you want as a result should D3 equal 14, 16, or 17.

Assuming in the first case that you will want to return a zero, then either:

=SUMPRODUCT((D1={2,3,4})*(D3>={14,16,17})*(D3-{14,16,17}*F1))

or

=SUMPRODUCT((D1={2,3,4})*(D3>{14,16,17})*(D3-{14,16,17}*F1))

depending on what you want to happen if D3 is exactly 14,16, or 17

might work for you.
--ron
 
Your solution worked great once I cleared out "ERROR". What I forgot to ask
is now if there is no data for the solution, I get a return of "FALSE" but
would prefer to have an empty cell indicated. I've tried to incorporate ""
into D5, but can't get to work.
 
Ron: If D1 is left blank I then wanted a return of "", sorry I didn't think
of it! Your solution gave me a hugh negative answer when there was data, and
"#VALUE" if there was no data.
 
Ron: If D1 is left blank I then wanted a return of "", sorry I didn't think
of it! Your solution gave me a hugh negative answer when there was data, and
"#VALUE" if there was no data.
Click to expand...

What if D1 is not = to 2,3 or 4?

The result being a negative number is solely a function of the values you have
in D1, D3 and F1

For example, if

D1=3
D3=18
F1 = 4

Then

If D1=3 and D3 is <16 then D5=0 , If D1=3 and D3 is >16 then D3-16*F1

D3-16*F1 =
18 - 16*4 =
18 - 64 = -46

The return of a VALUE error means that you have text in D1 or one of the other
cells.

You can eliminate those kinds of errors by wrapping the formula in an IF
statement.

e.g.

=IF(AND(COUNT(D1,D3,F1)=3, OR(D1={2,3,4})),
SUMPRODUCT((D1={2,3,4})*(D3>={14,16,17})*(D3-{14,16,17}*F1)),"")
--ron
 
Back
Top