SOLVED

Multiply formula with multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-3298188%22%20slang%3D%22en-US%22%3EMultiply%20formula%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3298188%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20that%20allows%20me%20to%20calculate%20commission%20based%20off%20of%20several%20scenarios.%20If%20they%20have%20less%20than%203%20deals%20(let's%20say%20in%20cell%20A1%20to%20make%20simple)%20than%20I%20need%20to%20multiply%20their%20revenue%20(in%20cell%20A2)%20by%20.75%2C%20if%20over%203%20deals%2C%20AND%20their%20revenue%20is%20under%20%2475K%20then%20multiply%20by%20.80%2C%20if%20over%203%20deals%2C%20AND%20their%20revenue%20is%20between%2076K-100K%20then%20multiply%20by%20.85%2C%20if%20over%203%20deals%2C%20AND%20their%20revenue%20is%20over%20%24100K%20then%20multiply%20by%20.90.%20I've%20tried%20using%20IF's%2C%20PRODUCT%20formulas%2C%20can%20anyone%20help%20me%20with%20this%20please%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22205%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2265%22%3ETOTAL%20DEALS%3C%2FTD%3E%3CTD%20width%3D%22140%22%3E%26nbsp%3BTOTAL%20REVENUE%20FOR%20AGENT%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2265%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22140%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2265%22%3E3%3C%2FTD%3E%3CTD%20width%3D%22140%22%3E%26nbsp%3B%24%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%2044%2C250.00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3298188%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302570%22%20slang%3D%22en-US%22%3ERe%3A%20Multiply%20formula%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302570%22%20slang%3D%22en-US%22%3EThis%20is%20fantastic!%20Thank%20you%20so%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302484%22%20slang%3D%22en-US%22%3ERe%3A%20Multiply%20formula%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302484%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1376397%22%20target%3D%22_blank%22%3E%40LisaMarie1981%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20sample%20workbook.%20I%20used%20two%20cells%20for%20the%203%20and%200.75%2C%20and%20a%20table%20for%20the%20other%20values.%3C%2FP%3E%0A%3CP%3EYou%20can%20edit%20the%20values%2C%20and%20if%20necessary%20expand%20the%20table.%20The%20formula%20will%20take%20the%20changes%20into%20account.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302468%22%20slang%3D%22en-US%22%3ERe%3A%20Multiply%20formula%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302468%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BHow%20would%20you%20adjust%20this%20if%20you%20wanted%20to%20grab%20the%20data%2Fnumbers%20in%20bold%20from%20actual%20cells%20instead%20so%20that%20if%20we%20ever%20had%20to%20change%20the%20percentages%20or%20numbers%20it%20would%20automatically%20update%3F%20Like%20a%20plug%20and%20play%20scenario%20I%20guess.%20Is%20that%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DA2*IF(A1%26lt%3B%3D%3CSTRONG%3E3%3C%2FSTRONG%3E%2C%20%3CSTRONG%3E0.75%2C%3C%2FSTRONG%3E%20LOOKUP(A2%2C%20%7B%3CSTRONG%3E0%2C%2075000%2C%20100000%7D%2C%20%7B0.80%2C%200.85%2C%200.90%7D)%3C%2FSTRONG%3E)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3298294%22%20slang%3D%22en-US%22%3ERe%3A%20Multiply%20formula%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3298294%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAMAZING!!!%20Thank%20you%20so%20much!%20That%20was%20driving%20me%20crazy!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3298200%22%20slang%3D%22en-US%22%3ERe%3A%20Multiply%20formula%20with%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3298200%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1376397%22%20target%3D%22_blank%22%3E%40LisaMarie1981%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DA2*IF(A1%26lt%3B%3D3%2C%200.75%2C%20LOOKUP(A2%2C%20%7B0%2C%2075000%2C%20100000%7D%2C%20%7B0.80%2C%200.85%2C%200.90%7D))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I need a formula that allows me to calculate commission based off of several scenarios. If they have less than 3 deals (let's say in cell A1 to make simple) than I need to multiply their revenue (in cell A2) by .75, if over 3 deals, AND their revenue is under $75K then multiply by .80, if over 3 deals, AND their revenue is between 76K-100K then multiply by .85, if over 3 deals, AND their revenue is over $100K then multiply by .90. I've tried using IF's, PRODUCT formulas, can anyone help me with this please?

 

TOTAL DEALS TOTAL REVENUE FOR AGENT 
  
3 $                44,250.00

 

 

5 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@LisaMarie1981 

=A2*IF(A1<=3, 0.75, LOOKUP(A2, {0, 75000, 100000}, {0.80, 0.85, 0.90}))

@Hans Vogelaar 

AMAZING!!! Thank you so much! That was driving me crazy!

@Hans Vogelaar How would you adjust this if you wanted to grab the data/numbers in bold from actual cells instead so that if we ever had to change the percentages or numbers it would automatically update? Like a plug and play scenario I guess. Is that possible?

 

=A2*IF(A1<=3, 0.75, LOOKUP(A2, {0, 75000, 100000}, {0.80, 0.85, 0.90}))

@LisaMarie1981 

See the attached sample workbook. I used two cells for the 3 and 0.75, and a table for the other values.

You can edit the values, and if necessary expand the table. The formula will take the changes into account.

This is fantastic! Thank you so much!