+ Start a Discussion
MikeyOMikeyO 

Average field

I am trying to figure out a formula that will let me do an average revenue per user (ARPU) on a per quarter basis.

What I am currently doing is (January revenue + February revenue + March revenue)/3

however, this is not accurate because if February or March numbers are not in yet, the divider causes the the ARPU to be lower than it actually is.

Can you help me with a formula for this?

Best Answer chosen by Admin (Salesforce Developers) 
Jake GmerekJake Gmerek

 (January revenue + February revenue + March revenue)/if(March revenue > 0, 3, if ( February revenue > 0, 2, 1))

 

Should do the trick.


All Answers

Jake GmerekJake Gmerek

 (January revenue + February revenue + March revenue)/if(March revenue > 0, 3, if ( February revenue > 0, 2, 1))

 

Should do the trick.


This was selected as the best answer
MikeyOMikeyO

That did the trick, thank you very much Jake!

Jake GmerekJake Gmerek

No problem!!

MikeyOMikeyO

Jake, one last question.

How would that work with a twelve month average?

(january_ASP__C +..... December_ASP__c )/if(December_ASP__c > 0, 12, if (November_ASP__c > 0, 11, 1))