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

Copper Contributor

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

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

# Re: How to Use Existing Value in a Column to Predict when a Specific Value Will be Reached or Exceed

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.

# Re: How to Use Existing Value in a Column to Predict when a Specific Value Will be Reached or Exceed

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))

# Re: How to Use Existing Value in a Column to Predict when a Specific Value Will be Reached or Exceed

@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?

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