function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MikeyOMikeyO 

Need help with a formula

Hello,

I have the following problem.

I have written the following formula, but I'm having difficulties if/when the January and/or February fields are empty

 

ASP= Average Selling Price

Currently I'm getting the following answer, but its not correct

January: $0.00

February: $0.00

March: $15.00

 

Q1 ASP: $5.00

when in reality Q1 ASP should be $15.00 since January and February were 0

 

 

Current formula for Q1 ASP = ( January_ASP__c + February_ASP__c + March_ASP__c )/if(March_ASP__c > 0, 3, if (February_ASP__c > 0, 2, 1))

 

Can anyone help me with this?

thank you,

 

Mike Oslin

Best Answer chosen by Admin (Salesforce Developers) 
Steve :-/Steve :-/

Try something like this:

 

( January_ASP__c + February_ASP__c + March_ASP__c ) / 
(
IF( January_ASP__c > 0, 1,0) + 
IF( February_ASP__c > 0, 1,0) +
IF( March_ASP__c > 0, 1,0)
)

 

All Answers

Steve :-/Steve :-/

Try something like this:

 

( January_ASP__c + February_ASP__c + March_ASP__c ) / 
(
IF( January_ASP__c > 0, 1,0) + 
IF( February_ASP__c > 0, 1,0) +
IF( March_ASP__c > 0, 1,0)
)

 

This was selected as the best answer
MikeyOMikeyO

Thank you Stevemo, that solved the issue.
Steve :-/Steve :-/

PS.  You owe me a beer!  ;-D  and I drink THESE!  http://beeradvocate.com/beer/profile/863/7971