Forum Discussion
Solved
Issue with IFS Statement Generation
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 o...
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)*4Hope that works for you.
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.
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.