Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Greater than less than
Im attempting to make something that AUTOMATICALLY creates a way to change a percentage...
our table goes like so.. 45% and up is 8% on another line anything between 44-41% is now 6% and 39% to 35% is 4% anythign less than 34% is 0% ..
This is my equasion I think im on the right track but it does not give me the right numbers except the
first 8% . This is my first equasion like this so ANY help would thanks....
=IF([Math Test 2]7 < 45, "8%", IF([Math Test 2]7 = 44, 43, 42, 41, "6%", IF([Math Test 2]7 = 39, 38, 37, 36, "4%", IF([Math Test 2]7 < 34, "0%"))))
so it shoud show like this
Markup is 45% commission is 8%, Markup is 40% Comission is 6%, Markup is 35% comission is 4%, Markeup is 34% and lower comission is 0% ...
Thanks for any help..
Comments
-
Hal,
Try using this formula below. Make sure you change the number after each bracket to correspond to the row in which you placed the formula. In my example I was using Row 1.
=IF([Math Test 2]1 >= 0.45, "8%", IF(AND([Math Test 2]1 < 0.45, [Math Test 2]1 >= 0.4), "6%", IF(AND([Math Test 2]1 < 0.4, [Math Test 2]1 >= 0.35), "4%", IF([Math Test 2]1 < 0.35, "0%"))))
Let me know if that works!
-
WOW that worked like a charm..
Not a spread sheet guy but learning
Mitch Thanks so much for your help..
Worked great
-
Also another math issue Im working on
=TotalSale1 - [Permit Cost]1 * Percent1 + [Deduction Addition]1
Its not doing anythign but copying the original Sale price into the
final comission box..
Wish I was more fluent on these thing...
We factor the comission without the permit
-
Not a problem, I'm happy to help! So you can understand and learn what I did, here are the things I altered to make your original formula work:
1. Make sure to use decimal points in your formula if you're working with percentages.
2. You used a less than sign (<) instead of a greater than sign (>) after the first cell reference. I also added an equals sign so that any value greater than or equal to 45% would result in 8%.3. Use AND statements when working with formulas that need to meet multiple criteria. Using an example from above, IF Math Test 2 is less than 45% AND greater than or equal to 40%, then use 6%.
As for your second question, could you send a screenshot of your sheet? I just want to get a better idea of what you're working on.
-
Sorry for the blur out its current client names... Anyways we want to take the Total Sales column subtract any Permit costs multiply our percentage (if any) to what remains from the total sale then Add (or subtract if a negative number) from the deduction addition Column to get the final pay. I should have the columns in that order but the math does not care whe it gets its info from.. Thanks
-
Ok, I think this is what you need:
=(([Total Sale]1 - [Permit Cost]1) * [Percent]1) + [Deduction Addition]1
Give that a try and let me know if it was what you were looking for.
-
Sorry this took so long to get back to you ...#INVALID OPPERATION error.
even after I fixed TotalSale... (no space) I have tried a few things but ended up with #UNPARSABLE... Again NOOB here
-
Hal,
#INVALID OPERATION would likely come from one of the cells being blank.
#UNPARSEABLE is usually just a missing paranthesis or bad spelling of a column name.
For the second one, if you double click on the cell with your formula, you should see the formula but with color coding for the cell reference. If the cell reference is normal blank text, that might be your problem.
Hope this helps.
Craig
-
Im still having a couple of issues.. I did split it up and found a work around.. .
I really do appreaciate both of your help
you have been amazing thanks
-
BTW my workaround went like this.
in a Cell far far away.. it a column called -permit =TotalSale1 - [Permit Cost]1
Then I do the rest of the needed math in the Final Pay column..
-
Mitch
I just jumped back on this and it appears the original percentage line you helped with
does not like being added a Math equasion... If I put 8% in and multipy that with my total sale it gives me the result.. If its from the amazing math you gave me it makes it unparasable.. The other math line you gave me works if the percentage is manually inputted...
Any ideas??
-
Hal,
I apologize for my delayed response. Just so I understand your question, when you manually enter a percentage the second formula works correctly. But when you use the percentage generated by the first formula it displays as unparsable. Did I understand that correctly?
-
Kate from Smart Sheet showed me what i did wrong with the equation. =IF([Mark UP]2 >= 0.45, 0.08, IF([Mark UP]2 >= 0.4, 0.06, IF([Mark UP]2 >= 0.35, 0.04, IF([Mark UP]2 < 0.35, 0))))
that fixed it and very happy with the results... =(TotalSale1 - [Permit Cost]1) * Percent1 + [Deduction Addition]1
Works wonders ....
