Issue with IFS Statement Generation

[carter375]

Hello!

I'm trying to create a budget and I've got some categories that determine what percent of my paycheck is going to go toward that savings category. I'm using an IFS statement to check all of the conditions, but I keep getting an error that tells me there's a problem with the formula, followed by, "Not trying to type a formula? When the first character is an equal (=) or..." 

What I want this formula to do is check to see if my input of paycheck amount is within a range of $200 to $299.99. If those conditions are met, then the formula multiplies my input by the percentage in the column next to it that I have chosen to take out and put into savings. The formula that I have created is:

=IFS(B20<=A4&(A5-0.01),((B20*B4)*4)), (B20<=A5&(A6-0.01),((B20*B5)*4)), (B20<=A6&(A6-0.01),((B20*B6)*4)), (B20<=A7&(A7-0.01),((B20*B6)*4)), (B20<=A8&(A8-0.01),((B20*B7)*4)), (B20<=A9&(A9-0.01),((B20*B8)*4)), (B20<=A10&(A10-0.01),((B20*B9)*4)), (B20<=A11&(A11-0.01),((B20*B10)*4)), (B20<=A12&(A12-0.01),((B20*B11)*4)), (B20<=A13&(A13-0.01),((B20*B12)*4)), (B20<=A14&(A14-0.01),((B20*B13)*4)), (B20<=A15&(A15-0.01),((B20*B14)*4))

What have I done wrong? I've attached a screenshot of the spreadsheet and formula I attempted to create. I'm almost positive that the formulas work individually.

[#2]

Just off the cuff, note that the & is for concatenating two strings, not for a conditional test. And the result will be text, not numeric value.

Say B20=20, A4=25, and A5=30.

B20<=A4&(A5-0.01) will give the result: 20<="2529.99"

In Excel text values are always greater than numerical values. Since "2529.99" is text your test will always be TRUE.

AND(B20<=A4, B20<=A5) is what you would use if you are testing whether or not b20 is less than/equal to both A4 and A5.

[#3]

@carter375 

Perhaps formula could be optimised, but at least such components as

B20<=A4&(A5-0.01)

are not correct. What do you mean by this?

[#4]

@Sergei Baklan 

By this 

B20<=A4&(A5-0.01)

I am trying to say if the entered amount in B20 is less than or equal to A4 and less than or equal to A5-0.01, which is part of the range my conditions are based on. If the tail end of my range was equal to A5 and not A5-0.01, I 

[#5 · Accepted solution]

@carter375  OK I think there are a couple problems with that formula the foremost being that I believe you can do it much easier.  As for the issues with the formula:

• the formula is trying to check a range and then multiply by the 'lower' row but you references to your "range" change from A4&(A5-0.01) to A15&(A15-0.01) 
• This "range" is not doing what you think: A4&(A5-0.01) will combine them like text and create a number 7079.99
• Around A6 and A7 you start referring to the row even before the 'lower' row
• as for why you are getting the error, could be doing a "<=" comparison to a text value that maybe couldn't get converted to a number or some other ref. but I didn't track that down

Now for the good news.  You can (and should) do this much easier using VLOOKUP:

=$B$20*VLOOKUP($B$20,$A$4:$B$14,2)*4

Hope that works for you.