Forum Discussion

Copper Contributor

Feb 04, 2021

Excel Formula Help

I am having a problem with a formula designed to calculate a commission rate that is taking 30% of the gross revenue. There is a minimum amount of $50 if the gross revenue is $50 or less. My formul...

Backup Revised Commission Report Template 2.3.2021.xlsx371 KB

Formulas and Functions

Riny_van_Eekelen

Platinum Contributor

Feb 04, 2021

ederdzinski Try this.

=IF(AND(NOT(ISBLANK(Q11)),Q11<=50),50,Q11*0.3)

and copy down.

ederdzinski

Copper Contributor

Feb 04, 2021

Thank you for your help! I am still seeing 50 in column S. Could that be caused by the formula in column Q that is subtracting revenue from expenses?

Riny_van_Eekelen

Riny_van_Eekelen
Platinum Contributor
Feb 05, 2021
ederdzinski Sorry, my mistake. Try this in S11 and copy down.
=IF(Q11=0,0,MAX(50,Q11*0.3))
- ederdzinski
  Copper Contributor
  Feb 05, 2021
  That did it! Thank you for your help!!
  
  Riny_van_Eekelen
  - Riny_van_Eekelen
    Platinum Contributor
    Feb 05, 2021
    ederdzinski Happy I could fix it for you!
ederdzinski
Copper Contributor
Feb 04, 2021
I also see that the minimum value of $50 wasn't calculating properly. I changed the formula using the MAX function, but still receive the value of 50 when no data is populated in column Q (which does have a formula subtracting values in column P from column O).

ederdzinski
- MSiva
  Copper Contributor
  Feb 05, 2021
  Hi ederdzinski,
  
  Please try this formula in column Q:
  
  =IF(OR(ISBLANK(Q15),(Q15=0)),50,Q15*0.3)
  - MSiva
    Copper Contributor
    Feb 05, 2021
    * formula in column S (not column Q), sorry 🙂