# Solved Dates and formula issue

Discussion in 'Business Applications' started by bleep69, Sep 19, 2019.

Joined:
Nov 21, 2008
Messages:
135
Hi there,

I am monitoring my electrical consumption via a spreadsheet, but the hectic power cuts here means that on some days I am unable to take a reading as the meter is off the whole time and doesn't display units. This is resulting in large spikes in my data, as when I get a reading it covers several days usage.

I am looking to insert a formula in every blank cell in the U column that will calculate how many days have passed since the previous reading, look at the difference in consumption between the start and the opening balance, and put the average power consumption for the missing days in the cell, and don't have a clue where to start. I could do it manually without much trouble, but thought this would be an interesting thing to learn if someone could take the time to show me how to do it.

Any help appreciated.

File size:
22.3 KB
Views:
6
2. ### byoung7998

Joined:
Dec 21, 2014
Messages:
15
I don't quite know what you mean by the "difference in consumption between the start and the opening balance" because aren't those the same numbers? Start and opening?

Anyway, an easy way to do at least a good part of what you want would be to use an =IFERROR(current function, what to do if current function returns an error).

Since your current function does a vlookup of the dates the readings were taken, if a particular date, like May 25, is searched for, that vlookup will return an "#N/A" error since it is not in your column A of the table.

The IFERROR function gives the result of the normal function you want, but if that function returns an error, then it will do the calculation after the comma inside the parenthesis.

So what you will want is (for Cell U2):

=IFERROR(VLOOKUP(T2,Table1[[Date]:[Remaining units]],2,FALSE),what to do if the date is not found in the tabular data)

Good luck!

Joined:
Nov 21, 2008
Messages:
135

The table in columns AB and C shows the dates on which I took readings, and what those readings were. The table in columns T and U is the same information, but with every day listed. I used VLOOKUP to insert the readings on the dates that readings were taken, but this leaves large blank periods during which I never managed to get readings. I want to fill in all the blank days in column U with the average power used on those days, which would be calculated by looking at the last balance, deducting the next reading, and dividing by the number of days between the 2. The problem is that the days between readings vary, and I do not know how to calculate that change via a formula.

4. ### byoung7998

Joined:
Dec 21, 2014
Messages:
15
You can do "math" with dates like any other number. Excel treats dates as numerical values (and partial dates as decimal parts of numbers). 1/1/1900 is considered date 1. 9/26/2019 is considered date 43,734. Each future day is 1 number higher than the previous day. So 9/27/2019 will be 43,735. The time between each day is then split up proportionately from 0.00000 (up to 15 decimals) to 0.99999 (also up to 15 decimals) 12:00:00 AM (midnight) is considered 0.0000, and it increments up until 0.99999 at 11:59:59 PM (fractional seconds are also used in the calculations). I've attached your spreadsheet with the results in column C.

This means you will no longer need columns T and U.

#### Attached Files:

• ###### zesa (1).xlsx
File size:
22.3 KB
Views:
1

Joined:
Nov 21, 2008
Messages:
135
Man, that is such a simple fix, should have been able to work that out myself!

Many thanks for the help, have a good day!