Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following function which I would like to have behave in the following way:
If the Sum Function (of the set of current selections minus the years 2011-2014) returns a value larger than a billion dollars, the number should display in billions ($#,##B). If not, and the value is larger than a million dollars, the number should display in millions ($#,##M). If not, and the value is larger than a thousand dollars, the number should display in thousands ($#,##K). If not, display in individual dollars ($#,##).
As it stands now, the SUM function works, but the Num Function doesn't seem to be working, other than inserting commas.
=IF(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000000000>1,NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount),'$#,###0B'),IF(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000000>1,NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount),'$#,##0M'),IF(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000>1,NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount),'$#,##0K'),NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount),'$#,##0'))))
Change the format. For instance '$#,##0.0B' for 1 decimal place etc.
It looks like you are not including the divisor in each of the sum functions.
=IF(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000000000>1,NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000000000,'$#,###0B'),IF(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000000>1,NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount),'$#,##0M')/1000000,IF(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000>1,NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount)/1000,'$#,##0K'),NUM(SUM({$-<FiscalYear={2011,2012,2013,2014}>}Amount),'$#,##0'))))
Ahhh, I misunderstood how NUM worked then. How do you designate how many decimal places it should round to then?
Change the format. For instance '$#,##0.0B' for 1 decimal place etc.
Got it! Thank you!