Jul 09 2020 07:29 AM
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.
Jul 09 2020 07:39 AM
Jul 09 2020 07:41 AM
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?
Jul 09 2020 08:04 AM
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
Jul 09 2020 08:21 AM
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:
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.
Jul 09 2020 08:21 AM
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:
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.