IFs and ORs

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?
 
S

Sheeloo

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.
 
S

Sheeloo

=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...
 
R

Ron Rosenfeld

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?


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
 
D

DavidT

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.
 
D

DavidT

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.
 
R

Ron Rosenfeld

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.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top