Nov 4, 2021

Can sheets calculate a formula untill a certain value?

I am creating a commission tracking spreadsheet. I need the sheet to calculate one way until a cap is met, then another way after, while keeping the (prior cap) results the same...I started with "=if(d2<2000, g2*.3, g2)" d2=fees which max out at 2k, g2=my commission, .3 is the % of fees. This works fine until i get to the cap of 5000, then it changes every previous calculation...is there a way to keep the previous calculations to their initial results?

Details

Sheets,Mac OS,Business

Locked

This question is locked and replying has been disabled.

Community content may not be verified or up-to-date. Learn more.

All Replies (7)

Gill K

Diamond Product Expert

Nov 6, 2021

Hi Mitchell,

I'm not sure what you mean by: until i get to the cap of 5000

Where does the 5000 come from? Or is that meant to be the 2000?

And what do you mean by it changes every previous calculation

Which previous calculations?

I think we need to see this in a sample sheet so you can show what is happening, and also what you would like to have happen.

To make it easier for us to develop and test possible solutions, and to demonstrate them for you, would you please take a look at this post about Best Practices When Asking a Question and share a sample sheet that has the permissions set for “Anyone with link” can EDIT? This will also guide you in avoiding sharing any sensitive data.

And this Blank Sheet Maker can create a shareable sheet for you - just add your sample data.

Thanks!

Gill

MITCHELL PELLERIN

Original Poster

Nov 12, 2021

Sorry, yes I meant 2000. I want to be able to track all sales/comissions for verification. each line will have sales transaction info, calculate fees, and commissions. the issue is that once a cap is met, the fee no longer applies, and i receive 100% of commission, but this only happens once the 2000k is reached. If the first 5 transaction are under the 2kcap, the 30% fees are taken out. if the 6th transaction reaches the cap, it doesnt take out the 30%, and then changes the first 5 transactions to not take out the 30% either. I trying to make the first transactions stay the same once the cap is met...is that possible? I will try and attach the sample sheet...

MITCHELL PELLERIN

Original Poster

Nov 12, 2021

https://docs.google.com/spreadsheets/d/1qwRQLYg8W-2nuicreIAA4I-Krj_D2-IOdXRgsyDpX9Q/edit#gid=2100307022

there is a #REF error, but the formulas are there to show what i intend to do...

Gill K

Diamond Product Expert

Nov 13, 2021

Thanks for sharing the sheet, Mitchell.

Can you clarify something?

If your first sale was for 225,000, for a commission of 6,750, the fees calculate as 2,025. Is this meant to be capped within the sale itself, so max of 2000, or do the full fees apply, and only on subsequent sales there would be no further fees?

Gill

Gill K

Diamond Product Expert

Nov 13, 2021

Hi Mitchell,

Have a look at tab GK.Help.

This formula in C2 calculates the fees owed for all the rows. It does require two helper columns, which you could hide, or move elsewhere if you prefer. I feel it could be done without the helper columns, but I haven't figued that out.

={"Fee";ArrayFormula(IF(B3:B="","",
  IF( F3:F -(B3:B*0.3) >= 2000,
    0,
    IF( ROW(B3:B)=ROW(B$3),
      B3:B*0.3,
      IF(SUMIF(ROW(E$3:E),"<="&ROW(E3:E),E$3:E)<=2000,
        E3:E,
        2000-SUMIF(ROW(E$3:E),"<="&ROW(E3:E)-1,E$3:E))))))}

Let me know if this helps.

Cheers,

Gill