0

I am trying to make this formula shown below return a blank cell instead of 0 if there is no data to be counted thus yet.

For awareness this worksheet is calculating average profit per day in each different month, obviously, as we are not yet in June there is no data, hence I would like it to return a blank value.

I cannot use custom number formats to hide the 0 as at the bottom of all the months, they are averaged and this gives an inaccurate answer.

=SUMIFS('SHARES LOG'!L:L,'SHARES LOG'!P:P,">="&DATE(2021,6,1),'SHARES LOG'!P:P,"<="&DATE(2021,6,30))/30

If anyone could tweak this that would be great.

Insider
  • 95
  • 12
  • Not very elegant but `if(formula=0,"",formula)`? – SJR Feb 16 '21 at 12:31
  • Hi thanks for your help, i have used the following : =IF('SHARES LOG'!L:L,'SHARES LOG'!P:P,">="&DATE(2021,1,1),'SHARES LOG'!P:P,"<="&DATE(2021,1,31)=0,"",'SHARES LOG'!L:L/31) ---- however this says i have entered too many arguments? – Insider Feb 16 '21 at 12:36
  • 3
    You'd need to repeat the whole SUMIFS formula in each part - unless you have a version of Excel that has `LET`. Alternatively, you could do something like: `=IFERROR(1/(1/SUMIFS('SHARES LOG'!L:L,'SHARES LOG'!P:P,">="&DATE(2021,6,1),'SHARES LOG'!P:P,"<="&DATE(2021,6,30)))/30,"")` – Rory Feb 16 '21 at 12:40
  • You have to repeat the whole formula posted in your question. – SJR Feb 16 '21 at 12:41

0 Answers0