Ask Search:
Daniel DowellDaniel Dowell 

Formula Field that says

There are 3 Currency Fields Estimated Revenue, Main Value and Pilot Value. I need a formula that shows in the a Total Field If Estimated Revenue has a value in it this displays in the Total, but if I have a Value in the Pilot Value is added then the Total Value Show the Pilot Value and If the Main Value has Value then this value show in the Total. But if all 3 have value it will still only display the Main Value in the Total. Hope this makes sense. So
Estimated Value = 10k, Total Value = 10k, and if Pilot Value is 5K then Total = 5k, but if Main Value = 50k then Total is 50k even if the other field have a vlaue in it
Best Answer chosen by Daniel Dowell
Amnon KruviAmnon Kruvi
Thanks! It looks like the fields are always populated, but sometimes they are populated with 0. This isn't the same as blank (or nil), so we should check for that specifically.

How about:
IF (Main_Value__c > 0, Main_Value__c, 
IF (Pilot_Value__c > 0, Pilot_Value__c, 
Estimated_Revenue__c))

 

All Answers

Amnon KruviAmnon Kruvi
Hi Daniel,

Did you mean something like this?
BLANKVALUE(Estimated_Revenue__c,
BLANKVALUE(Pilot_Value__c,
Main_Value__c))
Tony TrinhTony Trinh
++Amnon,
Do you have Estimated Revenue and Main Value reversed?
BLANKVALUE(Main_Value__c,
BLANKVALUE(Pilot_Value__c,
Estimated_Revenue__c))
Amnon KruviAmnon Kruvi
@Tony If I understand the requirement right, he wants Estimated Revenue as the top priority, then Pilot, then Main. In that case, the fields aren't reversed (the first one will win, therefore Estimated Revenue).

Of course, I could have just misunderstood the requirement.
Daniel DowellDaniel Dowell
To be fair the Main Value is the Priority, But they need the Total value to 
1 = Estimated Value = 10k then the Total = 10k
but 
Pilot Value = 5k and the Estimated Value = 10k then Total value = 5k
Main Value = 50k (even if Estimated Value =10k, and Pilot Value = 5K) total is 50k
Amnon KruviAmnon Kruvi
Ahhh, in that case I misunderstood. Use Tony's version. 
Daniel DowellDaniel Dowell
This does not show the Estimated Value in the Total Value when the Main and Pilot Values are nil, and the Pilot Value does not show only the Main Value shows in the Total Value. As I stated Main Value is the Priority but when this client is working an opportunity they start with the Estimated Value whch needs to be displayed in the Total Value Field, but when the Pilot Value has a Value then that value is displayed in Total Value and on course Main Value is what is displayed when it has a value 
Amnon KruviAmnon Kruvi
This formula should show the estimated value when the others are empty. If you look at the formula editor, right under it there is an option section where you can select to treat empty values as either blank, or as zero. Make sure that they are treated as empty and not as zero. 
Daniel DowellDaniel Dowell
Sorry I must be missing something as I have treated Blank fields as Blank, it does not display anything in the Total but Main Value. 
Amnon KruviAmnon Kruvi
Would you mind showing a screenshot of the formula, and one of the record showing the relevant fields? 
Tony TrinhTony Trinh
Daniel,

What happens if your main value is blank?
Daniel DowellDaniel Dowell
Screen Shots as requested FORMULA [cid:image001.png@01D59B2C.A0544040] [cid:image002.png@01D59B2C.FE3721F0] Daniel Dowell Salesforce Lead Tel +44 (0) 2392 825925 | Mob +44 (0) 7880 267 085 Website | Email | LinkedIn | Twitter
Amnon KruviAmnon Kruvi
Sorry, images don't go through by email. You'll need to attach them through the site. 
Daniel DowellDaniel Dowell
User-added imageFormula User-added imageUser-added imageUser-added imageShows each of them Populated
Amnon KruviAmnon Kruvi
Thanks! It looks like the fields are always populated, but sometimes they are populated with 0. This isn't the same as blank (or nil), so we should check for that specifically.

How about:
IF (Main_Value__c > 0, Main_Value__c, 
IF (Pilot_Value__c > 0, Pilot_Value__c, 
Estimated_Revenue__c))

 
This was selected as the best answer
Daniel DowellDaniel Dowell
I am so sorry, I can see I was not explaining myself correctly. That worked a treat, I really appreciate you patients and help
Amnon KruviAmnon Kruvi
No need to apologize! I was actually excited to see you use the term nil in the original post, and I didn't bother verifying even though the question was on my mind :)
Daniel DowellDaniel Dowell
HAHA, I am rather new to formulas and I am finding some of it tricky. Like if you have a start date of 1 Nov 2019 and an End Date of 1 April 2020 how to you get a formula field to tell you how many months that is. 
 
Amnon KruviAmnon Kruvi
Ooo, that's actually a very tricky one. A formula can tell you how many DAYS are between two days, and you can divide it by 3 to get an estimate.

You could also try something more accurate. Maybe something like:
((YEAR(End__c) - YEAR(Start__c)) * 12) + (MONTH(End__c) - MONTH(Start__c)) 

But that doesn't take into account the day of the month. So 31st January 2020 -> 1st February 2020 will still show 1 month.