Greater than less than

Hal White

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.. 

 

Mitch Shaffer

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!

Hal White

WOW that worked like a charm.. 

Not a spread sheet guy but learning

Mitch Thanks so much for your help..

Worked great

 

Hal White

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

Mitch Shaffer

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.

Hal White

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 

Mitch Shaffer

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.

Hal White

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

J. Craig Williams

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

 

 

 

Hal White

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

Hal White

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..

Hal White

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??

Mitch Shaffer

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?

Hal White

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 ....