Excel Formula Help

Copper Contributor

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 formula is calculating properly, but putting in a value of $50 for all cells where the gross revenue has not been entered.  I need this to be zero until an amount is entered into the gross revenue column.  

ederdzinski_0-1612459113994.png

How can I edit the formula to show zero (or leave blank) until data is entered in column Q?  This should be an easy fix, but I am drawing a blank?  Thank you for your help!!

8 Replies

@ederdzinski Try this.

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

and copy down. 

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?

 

ederdzinski_0-1612464026316.png

 

 

@Riny_van_Eekelen 

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_0-1612465767569.png

 

 

@ederdzinski 

Hi @ederdzinski,

 

Please try this formula in column Q:

 

=IF(OR(ISBLANK(Q15),(Q15=0)),50,Q15*0.3)

 

MSiva_0-1612511184877.png

 

* formula in column S (not column Q), sorry :)

@ederdzinski Sorry, my mistake. Try this in S11 and copy down.

=IF(Q11=0,0,MAX(50,Q11*0.3))

 

That did it!  Thank you for your help!!

 

@Riny_van_Eekelen 

@ederdzinski Happy I could fix it for you!