calculate percentage of pay based on days old
Okay, I think I got it all...
=IF(C2=1,IF(E2<99.99,25,E2*LOOKUP(D2,{0,16,30,41}, {0.1,0.15,0.2,0.25})),IF(E2<99.99,LOOKUP(D2,{1,31, 41},{25,50,100}),IF(AND(D2<16,E2*5%200),200,E2*LO OKUP(D2,{0,16,31,41},{0.05,0.09,0.12,0.15}))))
*Note: You didn't say what to use for used widgets over 41 days old. From
your pattern, I assumed 0.25
--
Best Regards,
Luke M
*Remember to click "yes" if this post helped you!*
"marsjune68" wrote:
Ok I have what seems to be a challenge for me.
I have widgets for sale both new and used widgets. When I sale one I want to
calculate the percentage I am paid based on conditions. In addition, I want
it to total it up in a cell.
These are my columns
€ścol a€ť a person initials €ś€ťI enter manually€ť€ť
€ścol c€ť widget used or new 1= used widget 2 = new widget €ś€ťI enter manually€ť€ť
€ścol d€ť #days old the widget is €ś€ťI enter manually€ť€ť
€ścol e€ť profit of the sale of the widget €ś€ťI enter manually€ť€ť
€ścol f€ť I want this total of profit based on conditions.
The row are individual sales I track I enter in different information.
My conditions a
1. New widget, 0-15 days old, get paid 5% of profit but to a maximum of
amount of $200.00
2. New widget, 16-30 days old, get paid 9%
3. New widget, 31-40 days old, get paid 12%
4. New widget, 41 and more days old, get paid 15%
5. Used widget, 0-15 days old, get paid 10%
6. Used widget, 16-30 days old, get paid 15%
7. Used widget, 31-40 days old, get paid 20%
I have minimums that I want to calculate as well in this mess
If the widget is new and over 41 days and the profit is under $99.99 I want
to pay $100.00
If the widget is new and between 31-40 days and the profit is under $$99.99
I want to pay $50.00
If the widget is new and between 1-30 days and the profit is under $$99.99 I
want to pay $25.00
If the widget is used and the profit is under $$99.99, I want to pay $25.00
no matter how old it is.
|