Formula Needed to calculate 2 different percentages with limits...

[excelteam777]

A1 = an amount EXP: 350,000
B1 = % #1 EXP: 3.00%
C1 = % #2 EXP: 2.00% (sometimes this cell will not have a % entered)
D1 = Formula

I need 2 different scenarios considered.

1st: If only B1 has a % I would like D1 to simply calculate 3.00% * 350,000 =10,500

2nd: If both B1 and C1 have % entered, I would like B1 to only multiply the value in A1 up to 100,000. Then have C11multiply by the remaining value for a final answer in D1.

EXAMPLE: B1 would have calculated 3,000 (3.00% * 100,000) and C1 would have calculated the remaining amount of 5,000 (2.00% * 250,000) for a total of 8,000 in D1

[martindwilson]

try
=IF(C1="",A1*B1,(MIN(A1,100000)*B1)+(MAX(0,A1-100000)*C1))

[excelteam777]

works great, thanks

[FDibbins]

self-deleted

[excelteam777]

try
=IF(C1="",A1*B1,(MIN(A1,100000)*B1)+(MAX(0,A1-100000)*C1))

I need to add a few steps to this one you did for me earlier.

It works great however Now in addition to the formula, I need to multiply the answer from your formula against 3 different % valued cells depending on the text selected in a 4th cell. The 3 % cells sit on another worksheet.

In my original final example, D1 = 8,000.

D1 = 8000
K1 = a letter EXP: "F"
Worksheet!E1 = a % EXP: 25% and represents the letter "R" if imputed into K1
Worksheet!F1 = a % EXP: 50% no letter representation for this one-just standard calculation
Worksheet!G1 = a % EXP: 75% and represents the letter "F" if imputed into K1

So if I entered nothing into K1 the answer in D1 should be 4000 (8000*the percentage amount displayed in Worksheet!F1)
If I enter "R" into K1 the answer in D1 should be 2000 (8000*the percentage amount displayed in WorksheetE1)
If I enter "F" into K1 the answer in D1 should be 6000 (8000*the percentage amount displayed in WorksheetG1)

[martindwilson]

just multiply it by
if(k1="",1,if(k1="r",Worksheet!E1,Worksheet!g1)

=IF(C1="",A1*B1,(MIN(A1,100000)*B1)+(MAX(0,A1-100000)*C1))*if(k1="",1,if(k1="r",Worksheet!E1,Worksheet!g1)

[excelteam777]

I wasn't sure how to input the additional cell value for "F" text so what I did was create an additional column for input of the % and then at the end of the original formula I added *k13
below is the actual cells I am using.

=IF(M13="",H13*L13,(MIN(H13,100000)*L13)+(MAX(0,H13-100000)*M13))*K13

I still need 1 more step. In cell Dashboard!B35 is where I need the balance of the calculation to populate.

Basically now here is how it currently operates.
N13 = formula
K13 = % entered EP: 25%
Dashboard!B35 = remaining balance % of K13 - in this case would be 75%

So if the total formula calculates 1000 as an answer, and in K13 I have entered 40%
then N13 should = 400 and Dashboard!B35 = 600
The rest of the formula works great just need to add this last function

[martindwilson]

this is starting to get hard to follow
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[excelteam777]

I attached it. Tab Agent 2 and Dashboard are the 2 to work with. Use my previous notes.

I just added a new workbook to show.

Basically whatever % I put into H9 on the second worksheet calculates the amount to K9

I want the balance % to also calculate and populate in Dashboard!E10

In the current sample workbook at 100% in H9 = 7000 in K(
If I input 40% into H9 then K9 will = 2800, I would like the remaining percentage (60%) to populate into Dashboard!E10 which would = 4200

[martindwilson]

just work out for 100% drop the %multiplier bit which is *H9
=IF('Agent 1'!J9="",'Agent 1'!F9*'Agent 1'!I9,(MIN('Agent 1'!F9,100000)*'Agent 1'!I9)+(MAX(0,'Agent 1'!F9-100000)*'Agent 1'!J9))
so then
in sheet
dashboard
=IF('Agent 1'!J9="",'Agent 1'!F9*'Agent 1'!I9,(MIN('Agent 1'!F9,100000)*'Agent 1'!I9)+(MAX(0,'Agent 1'!F9-100000)*'Agent 1'!J9))-'Agent 1'!K9