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 D5

=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"))))))

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

Seems to be Ok.

Note that I have added equality condition also...

On Sat, 25 Oct 2008 20:23:01 -0700, DavidT < XXXX@XXXXX.COM >

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

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.

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.

of it! Your solution gave me a hugh negative answer when there was data, and

"#VALUE" if there was no data.

On Sun, 26 Oct 2008 09:00:16 -0700, DavidT < XXXX@XXXXX.COM >

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

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

1. Need help sorting out ANDs and ORs in parameter query

2. Specify list items with unlimited ANDs and ORs?

3. multiple ifs & ands indirect/offset...need help desperately please

4. IF's & AND's

5. multiple ifs & ands indirect/offset...need help desperately pl

6. multiple ifs ands & indirect or offset?

7. two questions: and's inside if's and checking the return code of a program

8. Multiple ifs, and ands, and are not working

9. Ifs and Ands - I'm confused!

10. Nesting IF's and AND's - a bit of help please...

11. "ifs", "ands" & "vlookups"

12. New IFS interface (jfs.ifs, udf.ifs for example)

13. Why there is a need of IFS and non-IFS LSP sockets?

14. Small (JFS.IFS or HPFS.IFS AutoCheck) install bump, Solved!

15. IFs, ELSEs, END IFS -- proper construction

9 post • Page:**1** of **1**