Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate percentage of pay based on days old
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
calculate percentage of pay based on days old
Everything seems to work fine. Thank you! But let me ask this I can not
seem to figure it out. Here is the other condition. if the widget is NEW and is under 60 day old I want to put a cap on the profit paid to 200 max. If the widget is over 60 day then it will pay the 10%. so if the profit on a widget is 3000 and it is over 60 days old it would pay 10% and that =300. but if the profit on a widget is 3000 and it is under 60 days old it would pay 10% and that =300 I do not want it to pay 300 but to pay 200 instead. How can I put a cap on it to only report paying 200 in stead of 300 this would go in "col f"? How do I do that. Thank you again M "Luke M" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Percentage Based On Number | Excel Discussion (Misc queries) | |||
Calculate % of events based on days worked | Excel Worksheet Functions | |||
How can I calculate commissions that are not percentage based? | Excel Discussion (Misc queries) | |||
Calculate number based on percentage | Excel Worksheet Functions | |||
Calculate percentage based on cells with conditional formatting | New Users to Excel |