How to Use Existing Value in a Column to Predict when a Specific Value Will be Reached or Exceeded

Copper Contributor

It has been decades since I use Excel on a daily basis; so I have forgotten a lot that I once knew.  I use it nowadays only for household tracking.

 

I have a workbook wherein I track electricity consumption for each month by recording daily meter readings in a specific column.  I would like to be able to use the increasing values to predict when their sum will reach or exceed 1,000 kilowatts.  Having found that information, I will then consult the day of the billing cycle column and put that value in a table of other interesting values.

 

I have no clue how to do that and my search fu has been weak (as it must be for an octogenarian).

 

Please point me to wiki or tutorials that I can learn from.  I'm not literate in VBA, so using existing Excel functions is a must.

 

TIA

3 Replies

@JoseKOwl 

 

I have no clue how to do that and my search fu has been weak (as it must be for an octogenarian).

 

Let's not go disparaging ourselves (I say as another octogenarian). We can still excel at using Excel. It may take a while, but if you did it once, you can re-learn. 

 

It has been decades since I use Excel on a daily basis; so I have forgotten a lot that I once knew.  I use it nowadays only for household tracking.

 

That's what I do too. Anything to keep me learning. And I answer questions now and then here on this forum, which has made it possible for me to discover some of the newer functions that didn't exist back in the day. I also rely on the many amazing functions rather than VBA and macros.

 

Anyway, may I suggest that you post a copy of that energy consumption spreadsheet on OneDrive or GoogleDrive, with a link pasted here that would grant us access? That way we could make specific suggestions in response to your question, responses tailored to the way you're doing your tracking.

 

As to resources that could help you re-learn Excel:

@JoseKOwl 

Let's say you have dates (or day numbers) in A2 and down, and meter readings in B2 and down.

Today (the 7th of April), you'd have data in A2:B8.

The day when 100 is reached is given by

 

=LET(x, A2:A8-A2+1, y, B2:B8, parms, LINEST(y, x), (1000-INDEX(parms, 2))/INDEX(parms, 1))

@JoseKOwl  wrote:  ``predict when their sum will reach or exceed 1,000 kilowatts``

 

Wow, just 1000 kWh?!  Your usage must be a lot more frugal than mine.  Good for you!

 

Is this what you want?

 

JoeUser_0-1680937281461.png

Formulas:

C2: =IF(B2="", "", SUM($B$2:B2)))

D3: =IF(B3="", "", INT(FORECAST(1000, $A$2:A3, $C$2:C3)))

Copy C2 and D3 down their columns

Format D3:D8 etc as some form of Date; otherwise, you will see an integer