HI Everyone,
I am trying to calculate readings based on readings entered previously and then readings entered after the date. I know this might be a complex formula or simple for some, but I can't seem to figure it out.
<tbody>
</tbody>
Information in the readings column will pull from another page which the date is entered. I need to be able to calculate the other readings based off this information, so basically take an average and add it till I get to the next manually entered reading. I need a formula that will pull the first reading and then the next number reading.
Example: For 11-2-2018 = ((60)+(((100-60)/((11-5-2018)-(11-1-2018)))*((11-2-2018)-(11-1-2018)
What I have so far: =LOOKUP(1E+100,B$3:B3)+((($B$9-(LOOKUP(1E+100,$B$3:B3)))/($A$9-$A$3))*(A3-$A$3)) (*THE NUMBERS ARE BASED OFF MY EXCEL SHEET****)
The only number that should change when you pull the formula down is the one that is bold. Then the entire formula should change once you get to 11-5-2018 because it will pull the 100 over and then start averaging again based off the new values.
I know this is long, but I would appreciate any help you could offer
I am trying to calculate readings based on readings entered previously and then readings entered after the date. I know this might be a complex formula or simple for some, but I can't seem to figure it out.
Date | Readings | Calculated Reading |
11-1-2018 | 60 | 60 |
11-2-2018 | #N/A | |
11-3-2018 | #N/A | |
11-4-2018 | #N/A | |
11-5-2018 | 100 | 100 |
11-6-2018 | #N/A | |
11-7-2018 | #N/A | |
11-8-2018 | #N/A | |
11-9-2018 | 150 | 150 |
<tbody>
</tbody>
Information in the readings column will pull from another page which the date is entered. I need to be able to calculate the other readings based off this information, so basically take an average and add it till I get to the next manually entered reading. I need a formula that will pull the first reading and then the next number reading.
Example: For 11-2-2018 = ((60)+(((100-60)/((11-5-2018)-(11-1-2018)))*((11-2-2018)-(11-1-2018)
What I have so far: =LOOKUP(1E+100,B$3:B3)+((($B$9-(LOOKUP(1E+100,$B$3:B3)))/($A$9-$A$3))*(A3-$A$3)) (*THE NUMBERS ARE BASED OFF MY EXCEL SHEET****)
The only number that should change when you pull the formula down is the one that is bold. Then the entire formula should change once you get to 11-5-2018 because it will pull the 100 over and then start averaging again based off the new values.
I know this is long, but I would appreciate any help you could offer