IF Statement

[tennisbuck12]

Team,

How would i rewrite this formula so the middle part is greater 6 and less then 18. I bolded it.

=IF(ISERROR(B9+1),100,(IF(B9<=6,(6-B9)/6*50+100,IF(B9<=18,IF((18-B9)/12*50+50,B9>18,0)))))

 

[Joe4]

Re: IF Statement (Should be easy, just not for me)

Try:

[B]IF(AND(B9>=6,B9<=18),...[/B]

 

[tennisbuck12]

Re: IF Statement (Should be easy, just not for me)

Try:

[B]IF(AND(B9>=6,B9<=18),...[/B]

Works like charm!

 

[tennisbuck12]

Re: IF Statement (Should be easy, just not for me)

One last question. It returns false. Do you know why?

 

[Joe4]

Re: IF Statement (Should be easy, just not for me)

That usually means you you left out a FALSE argument, and it is hitting that FALSE condition (none of your conditions are being met).

Please post your whole formula, as you have it, and explain exactly how this function should be working.

 

[tennisbuck12]

Re: IF Statement (Should be easy, just not for me)

That usually means you you left out a FALSE argument, and it is hitting that FALSE condition (none of your conditions are being met).

Please post your whole formula, as you have it, and explain exactly how this function should be working.

The value for cell B9 is 8

Here's the formula

=IF(ISERROR(B9+1),100,(IF(B9<=6,(6-B9)/6*50+100,IF(AND(B9>=6,B9<=18),IF((18-B9)/12*50+50,B9>18,0)))))

 

[Joe4]

Re: IF Statement (Should be easy, just not for me)

Note that in your new IF clause with the AND, if the conditions are met, you have it going directly to another IF.
Without knowing exactly what you are trying to do, I cannot tell you if your logic is sound.

I am thinking that your formula can possibly be simplified, but can you just explain in plain English what you want it do do?

 

[tennisbuck12]

Re: IF Statement (Should be easy, just not for me)

Note that in your new IF clause with the AND, if the conditions are met, you have it going directly to another IF.
Without knowing exactly what you are trying to do, I cannot tell you if your logic is sound.

I am thinking that your formula can possibly be simplified, but can you just explain in plain English what you want it do do?

=IF(ISERROR(B9+1),100,(IF(B9<=6,(6-B9)/6*50+100,IF(AND(B9>=6,B9<=18),IF((18-B9)/12*50+50,B9>18,0)))))

Basically if the value is null its 100
If B9 is less then 6 calculate that formula
If B9 is greater then 6 and less then 18 caluclate IF((18-B9)/12*50+50
And if greater then 18 it should =0

Does that help?

 

[tennisbuck12]

Re: IF Statement (Should be easy, just not for me)

=IF(ISERROR(B9+1),100,(IF(B9<=6,(6-B9)/6*50+100,IF(AND(B9>=6,B9<=18),IF((18-B9)/12*50+50,B9>18,0)))))

Basically if the value is null its 100
If B9 is less then 6 calculate that formula
If B9 is greater then 6 and less then 18 caluclate IF((18-B9)/12*50+50
And if greater then 18 it should =0

Does that help?

This is the orginal formula

IFS(C3<=6,(6-C3)/6*50+100,C3<=18, (18-C3)/12*50+50,C3>18, 0)

Where C3 is the same as B9. My version of excel doesn't support IFS

 

[Joe4]

Re: IF Statement (Should be easy, just not for me)

I see the issue:

If B9 is greater then 6 and less then 18 caluclate IF((18-B9)/12*50+50

We already accounted for less then 18 in our AND clause, so you don't need another IF for it.
You should just be able to use:

=IF(B9="",100,IF(B9<6,(6-B9)/6*50+100,IF(AND(B9>=6,B9<=18),[COLOR=#333333](18-B9)/12*50+50[/COLOR],0)))