Help adding a % to a give cell

[imtaylor]

Hello and thanks for reading this thread. I'm working with a materials order sheet I've created and I would like the final cell - the one that gives the amount of product to order to be increased by "X" percent, but not the same percent the larger the order gets. The additional percent is to cover drop and waste factors.

Example: Order 50(plus 3%) = 53 ... but when I need to order 500 I don't need the additional waste of a full 6%, I might consider a max number of say 10 and not 30.

I was hoping there is a better way to accomplish this without creating a long formula of "IF" statements. Any guidance would be most appreciated!

Taylor

 

[SkipVought]

"Example: Order 50(plus 3%) = 53 "

3% of 50 is 1.5 NOT 3.

"but when I need to order 500 I don't need the additional waste of a full 6%"

Where did 6% come from? 3% of 500 is 15.

Consider a TABLE that has ranges, and for each range a percentage.

The the additional order amount is a result ov a LOOKUP.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

3% of 50 is 1.5, not 3.

But you could do something like:
[tab]=Min(10, PartsOrdered * Percentage)


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[imtaylor]

I see I made a typo ... the 3% should have been 6%.

 

[imtaylor]

Thanks for the ideas!

higgins, the order numbers range from 1 to several hundred. I guess I could nest a few "IF" in there to set what the default numbers for the =MIN should be.

Working in overload mode on this one ... trying to keep it as easy for my boss to understand.

Taylor

 

[AnotherHiggins]

If the percentage and minimum is variable based on the quantity, then I'd go with Skip's suggestion and build a table (on another sheet) with what you want.

It would look something like:
[tt]
Qty Ordered Percentage
0 0.10
100 0.20
200 0.30[/tt]

Then, as Skip said, you can use a vlookup to return the proper percentage, like
[tab]=A2 * VLOOKUP(A2, LookupTable, 2)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[imtaylor]

Cool!! This will make things easier to deal with.

Thanks for the help guys!

Taylor