excelvbaexcel-formulaexcel-2019

How to hide 0's when there is no data to calculate without using custom number formats


Below is part of a financial worksheet to calculate profit and loss.

Currently C19 shows £ 0.00 as there is no data in the next to calculate an answer.

I want cell C19 to return blank until such a point there is data in the next sheet to calculate an answer but NOT using custom number formats (as I am already using one that I would like to keep)

Custom number format already in use in cell C19 : + £ #,##0.00;- £ #,##0.00; 0.00

This is currently the formula in cell C19 : =SUMIF('SPREADS LOG'!A:A,"*OPEN*",'SPREADS LOG'!K:K)

Can anyone suggest the correct formula needed?


Solution

  • I see 4 solutions:

    1. Change your cell formating to: + £ #,##0.00;- £ #,##0.00;
    2. Change the formula as the previous answer, but if you have 365 you can use the new LET function to calculate the SUMIF only once:
      =LET(mySUMIF,IF(SUMIF('SPREADS LOG'!A:A,"OPEN",'SPREADS LOG'!K:K)=0,"",SUMIF('SPREADS LOG'!A:A,"OPEN",'SPREADS LOG'!K:K)),IF(mySUMIF=0,"",mySUMIF))
    3. Use Conditional formatting to have the same color of the cell as the background.
    4. Change the Excel setting to not display any 0 values:
      File > Options > Advanced
      Select your worksheet and then unselect the option Show a zero in cells that have zero value.