CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2019
Insider
Insider

Reputation: 105

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?

Upvotes: 0

Views: 733

Answers (3)

Oscar
Oscar

Reputation: 1

To avoid changing formatting my go to when hiding zeros is to rely on the fact that divide by zero will produce an error. So =IFERROR(1/(1/value),"") will produce blanks if value is ever zero and will otherwise produce the value. It avoids typing in a separate check for zero entries.

In the case in question it would just be:

=IFERROR(1/(1/SUMIF('SPREADS LOG'!A:A,"*OPEN*",'SPREADS LOG'!K:K)),"")

Upvotes: 0

Florin
Florin

Reputation: 397

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.

Upvotes: 1

Not just for fun
Not just for fun

Reputation: 39

You can move the number format to TEXT formula:

=TEXT(SUMIF('SPREADS LOG'!A:A,"*OPEN*",'SPREADS LOG'!K:K);"+ £ #,##0.00;- £ #,##0.00; ")

There is a optimization problem using

IF(SUMIF(...)=0,",SUMIF())

since you'll end calculate the sum twice.

Upvotes: 1

Related Questions