I am at a bit of a loss. For any given month I am trying to calculate some stats. Column A has the date, Column B is a numerical value. In an ideal world column B would always have a value, but sometimes that number is calculated and it can only be calculated on the given day. As a result I need to get creative with my formulas to account for potential blanks.
The stat I am trying to calculate for requires a COUNTBLANK from the last day of data entry to the last day of the current month. For this example we will use January below. January 1 row in the table below starts at A9 in a spreadsheet if you want to try to duplicate this craziness.
The formula in question is and I put it in F11:
=IF(F10="","",If(B39="","Pot addl loss: "& Round((Round(F$2/(count(B$9:B$39)),1)*-1)*(COUNTBLANK(INDEX(B9:B39,MATCH(1E+100,B9:B39)):B39)),1),""))
Above F10 = the difference between the first row of data and the last row 250-243 so... 7
And F2 = the total some of what would be each months F10s respectively. So for this example just call it 7 as well
When I have no data beyond B39 (1/31 date) it calculates as expected (-20.9). But as soon as I put in any number in B40 or greater (I put 244 in a cell) it takes those numbers in to account and changes the output value (-22) when I would expect this number to not change because >=B40 is outside the range.
Help? Thoughts?
| A | B |
|---|---|
| 1/1/25 | 250 |
| 1/2/25 | |
| 1/3/25 | 251 |
| 1/4/25 | 250 |
| 1/5/25 | |
| 1/6/25 | |
| 1/7/25 | |
| 1/8/25 | 249.1 |
| 1/9/25 | |
| 1/10/25 | 249.2 |
| 1/11/25 | |
| 1/12/25 | 247 |
| 1/13/25 | 246.5 |
| 1/14/25 | |
| 1/15/25 | 250 |
| 1/16/25 | |
| 1/17/25 | 246 |
| 1/18/25 | 245.2 |
| 1/19/25 | |
| 1/20/25 | 243 |
| 1/21/25 | |
| 1/22/25 | |
| 1/23/25 | |
| 1/24/25 | |
| 1/25/25 | |
| 1/26/25 | |
| 1/27/25 | |
| 1/28/25 | |
| 1/29/25 | |
| 1/30/25 | |
| 1/31/25 | |
| 2/1/25 |