0

I am trying to work out the average profit or loss per day in any given month.

However my formula is only averaging number between the days currently present, I know that somehow I need the total amount (at any point so far) during the month to be divided by 31.

If there is no data, however (for a future month, for example, the cell should remain blank)

This is the formula I am currently using : =IFERROR(1/(1/AVERAGEIFS('SHARES LOG'!L:L,'SHARES LOG'!P:P,">="&DATE(2021,1,1),'SHARES LOG'!P:P,"<="&DATE(2021,1,31))),"")

If anyone could tweak this that would be great.

Insider
  • 95
  • 12
  • Just use `SUMIFS` with the same criteria, and divide by the number of days in the month. – Ron Rosenfeld Feb 15 '21 at 19:27
  • Hi @RonRosenfeld I have just tried that but my formula must have been wrong as it came back with 'there's an error in your formula'. How would you lay it out? – Insider Feb 15 '21 at 20:54
  • What did you do? Does your original formula work? If not, fix that. If it does, make the changes one step at a time. – Ron Rosenfeld Feb 16 '21 at 00:10
  • i used '''=SUMIFS('SHARES LOG'!L:L,'SHARES LOG'!P:P,">="&DATE(2021,2,1),'SHARES LOG'!P:P,"<="&DATE(2021,2,28))/28''' Which worked however it still populated '0' in the future dated columns. Is there any way to fix this without custom number formats? As way below i am trying to average all the numbers and the '0's are messing with it. – Insider Feb 16 '21 at 10:02
  • If the problem is returning a blank if the entire month is not yet populated, then use `COUNTIFS` to test. eg. `if(countifs(,,,,,,)=0,"",your_formula)` – Ron Rosenfeld Feb 16 '21 at 11:04
  • I have tried this and am now using --- =IF(COUNTIFS('SHARES LOG'!P:P,">="&DATE(2021,2,1),'SHARES LOG'!P:P,"<="&DATE(2021,2,28)=0,"",=SUMIFS('SHARES LOG'!L:L,'SHARES LOG'!P:P,">="&DATE(2021,2,1),'SHARES LOG'!P:P,"<="&DATE(2021,2,28))/28 ---- however again i get an error message. – Insider Feb 16 '21 at 11:15
  • You are missing a `)`. Write out the formulas one part at a time to see where your error is. (Or use the formula evaluation tool, but that's more difficult). – Ron Rosenfeld Feb 16 '21 at 11:21
  • I have just tried this, still cant figure it out! Thanks for your help anyway, Ron! – Insider Feb 16 '21 at 11:32
  • `COUNTIFS('SHARES LOG'!P:P,">="&DATE(2021,2,1),'SHARES LOG'!P:P,"<="&DATE(2021,2,28))=0` Note where you neglected to "close" your countifs formula. – Ron Rosenfeld Feb 16 '21 at 11:41
  • Thanks, Ron will try this now. – Insider Feb 16 '21 at 12:25

0 Answers0