Excel - IF statement with 1 of 3 possible outcomes 1

[mgilbertson]

I need some assistance in completing a formula. I am trying to write a formula that must show 1 of 2 results. See example:

Cell B16: 10
Cell L16: $0.30
Cell M16: $1.30

Cell Q16 must give one of the following results (b16*0.32) if M16=0, or (b16*0.32)+1 if M16>0, or 0 if neither is true.

Here is my original formula: =IF(OR(L16>0,M16>0),(B16*0.32)+(1),0). Which does work. But I recently found out, that if M16=0 then the added factor 1 is not needed.

I am usually pretty good when it comes to IF statements. Just not seeing this one though.

Thank you for any help.

 

[SkipVought]

Hi,,

=IF(M16=0,B16*0.32,IF(M16>0,B16*0.32+1,0))

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mgilbertson]

SkipVought,

Thank you for the formula. My apologies, I forgot to mention, if L16=0, then the answer should be zero.

My original formula accounts for that.

Thank you.

 

[SkipVought]

...and what's the else result supposed to be?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

Or (just for fun):

=B16*0.32*(M16>=0)+(M16>0)

 

[mgilbertson]

Result #1: $3.20 - if only L16 has a value

Result #2: $4.20 - if L16 and M16 has a value

Result #3: $0.00 - if neither L16 nor M16 has a value

Those are the 3 possible results I am trying to achieve. L16 and M16 are filled in based on drop down choices from another area.

 

[SkipVought]

If only L16 has a value"

Sorry pal! ALL of them have a value!!!

Please restate your requirement clearly, concisely and completely.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mgilbertson]

=IF(OR(L16>0,M16>0),(B16*0.32)+(1),0) - this is my original formula.

If L16 and M16 has a value larger than 0, then B16 is multiplied by 0.32 and factor 1 is added.
If Only L16 has a value larger than 0, then B16 multiplied by 0.32.
If neither has a value larger than 0, then a null result is the outcome.

I am looking for a formula that will do the following:

#1
B16: 10
L16: $0.30
M16: $1.30
Result: $4.20

#2
B16: 10
L16: $0.30
M16: $ -
Result: $3.20

#3
B16: 10
L16: $ -
M16: $ -
Result: $ -

The values in cells L16 and M16 are not part of the equation. Other than if the value in them is larger than 0.

I apologize for the confusion.

 

[SkipVought]

You want a hard number result, and not a formula result?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mgilbertson]

That is correct. But we need the ability to change the numbers in the formula (b16*0.32)+1. For example, bump the 1 to 2, or 0.32 to 0.50.

 

[SkipVought]

Well then you last requirement is not complete! You have, yet again, changed the requirement!

Please restate you requirement CLEARLY, CONCISELY and COMPLETELY.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mgilbertson]

I'll try one last time.

B16: 10 - manual variable
L16: $0.30 - variable that changes based on choices from a drop down table
M16: $1.30 - variable that changes based on choices from a drop down table
Q16: $4.20 - formulated result based on If(and(L16>0,M16>0),(B16*0.32)+1,0) **0.32 and 1 are variable factors for determining labor costs**

Depending on if L16,and M16 have a positive value above 0, the result in Q16 can change.

For example:

B16: 12
L16: $0.32
M16: $1.44
Q16: $4.84 <- is the result

B16: 9
L16: $0.30
M16: $ -
Q16: $2.88 <- is the result

B16: 15
L16: $ -
M16: $ -
Q16: $ - <- is the result

I hope this clarifies it a little more. I know this is a multiple IF statement. Just can't get the formula to look beyond the first logical statement.

 

[SkipVought]

If L16 and M16 has a value larger than 0, then B16 is multiplied by 0.32 and factor 1
If Only L16 has a value larger than 0, then B16 multiplied by 0.32.
If neither has a value larger than 0, then a null result is the outcome."

=IF(L16>0,IF(M16>0,B16*0.32+1,B16*0.32,0),0)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mgilbertson]

I believe that worked. I just tried it out. Got all 3 results.

Thank you very much!!!!

 

[strongm]

Your OP also stated that the result should be 0 if M16<0

Is that no longer the case?

If it is, then here's the silly alternative solution:

=(B16*0.32*(M16>=0)+(M16>0))*(L16>0)