2

I have a master sheet with values of what I would sell for. I want to create a formula or rules where I can subtract commission based on the value of the cell. I want to be able to edit from the table only so I don't have to mess around with hundreds of cells formulas when things change. I also don't want to just take commission by percentage. I know how to link the cells. I want a formula that will look in the table and say hey its between the two values so ill extract this amount of commission. I have attached a picture of an example of the rules table.

I've tried doing IF statements and ran into too many arguments issues.

I expect the formula to look in my table and take out the proper commission beside it.

Sample Table

player0
  • 124,011
  • 12
  • 67
  • 124
Akkasca
  • 47
  • 6

2 Answers2

1
=ARRAYFORMULA(Main!B2-VLOOKUP(Main!B2, 
 {REGEXEXTRACT(Comission!$A$3:$A$13, "\d+")*1, Comission!$B$3:$B$13}, 2))

0

player0
  • 124,011
  • 12
  • 67
  • 124
0

you can do various things like:

=ARRAYFORMULA(IF(A9:A<>"", IF(COUNTIF(A9:A, A9:A)>1, 
 B9:B-(B9:B*IFERROR(VLOOKUP(B9:B, 
 {{REGEXEXTRACT(A3, "\d+")*1, -B3%  };
  {REGEXEXTRACT(A4, "\d+")*1, -B4%};
  {REGEXEXTRACT(A5, "\d+")*1, -B5%};
  {REGEXEXTRACT(A6, "\d+")*1, -B6%};
  {400, 0}}, 2))), 
 B9:B-(B9:B*IFERROR(VLOOKUP(B9:B, 
 {{REGEXEXTRACT(C3, "\d+")*1, -D3%  };
  {REGEXEXTRACT(C4, "\d+")*1, -D4%};
  {REGEXEXTRACT(C5, "\d+")*1, -D5%};
  {REGEXEXTRACT(C6, "\d+")*1, -D6%};
  {400, 0}}, 2)))), ))

0

assuming Ema is a reseller and Jane & Yuki are one-timers

alternatives: https://webapps.stackexchange.com/q/123729/186471

=ARRAYFORMULA(IF(A2:A<>"", IFERROR(VLOOKUP(A2:A, Main!A2:B, 2, 0))-
 IFERROR(VLOOKUP(IFERROR(VLOOKUP(A2:A, Main!A2:B, 2, 0)), 
 {IFERROR(REGEXEXTRACT(Comission!A3:A, "\d+")*1), Comission!B3:B}, 2)), ))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • This has got me going in the right direction. However im in a little confusion from my lack or experience with the Array formula. I have separate sheets that get distributed to Re-seller clients and One time clients. The formulas on these pages will be manipulated so that it points to the pertaining customer type in the commissions sheet. I hope i am making sense :) – Akkasca Sep 29 '19 at 14:56
  • There's 4 sheets involved Main - This holds the value im being paid. Commission- Holds the table with both Re-seller and One timers values to subtract. Then (2 one for each client type) Distribution- This is that the clients will receive to look at their prices. – Akkasca Sep 29 '19 at 14:59
  • `=ARRAYFORMULA(IF(A9:A<>"", B9:B-(B9:B*IFERROR(VLOOKUP(B9:B, {{REGEXEXTRACT(A3, "\d+")*1, -B3% }; {REGEXEXTRACT(A4, "\d+")*1, -B4%}; {REGEXEXTRACT(A5, "\d+")*1, -B5%}; {REGEXEXTRACT(A6, "\d+")*1, -B6%}; {400, 0}}, 2))), ))` this is how you apply only purple rule set for prices in B9:B - https://i.stack.imgur.com/qcvPh.png – player0 Sep 29 '19 at 15:04
  • I still think this wont work because i update one cell then it apply it to everything. The sheet is also a little complex i made a sample here that is editable. https://docs.google.com/spreadsheets/d/1QkZID-grlTPmSyeLqsz0VOZ0WTc_Uee32j8j0GkQb1Y/edit?usp=sharing – Akkasca Sep 29 '19 at 16:28
  • I want to subtract it yes but i dont want to look up by item. I just care about subtracting commission based off what i will be paid which depends on the client type. – Akkasca Sep 29 '19 at 17:30
  • I added a formula that I got to work however I don't understand how to look up between the two numbers in the one cell. So right now it will only work if the exact value is there when it does VLookup. – Akkasca Sep 30 '19 at 05:24
  • change 0 to 1 in your VLOOKUP formulae – player0 Sep 30 '19 at 09:14
  • ii entered 1 but it still gets the error - did not find in the vlookup evaluation. This is the code `=(Main!B3)-VLOOKUP(Main!B3,(Comission!$C$3:$C$13):(Comission!$D$3:$D$13),2,1)` i updated it in the sheet also. – Akkasca Sep 30 '19 at 13:43