Excel Formula

[kloving24]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Readings[/TD]
[TD]Calculated Reading[/TD]
[/TR]
[TR]
[TD]11-1-2018[/TD]
[TD]60[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]11-2-2018[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11-3-2018[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11-4-2018[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11-5-2018[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]11-6-2018[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11-7-2018[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11-8-2018[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11-9-2018[/TD]
[TD]150[/TD]
[TD]150[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[KolGuyXcel]

Trying to be on the same page!

I understand that the columns with the headings Date and Readings have the data on which you want to work.

Is the "Calculated Reading" the output column? Or some other column?

Also, what outcome are you expecting? Like this?

Output Column (Based on the data in the columns with headings Date and Readings)

60
60
60
60
100
100
100
100
150

Post the way you want the results to be displayed if I'm wrong!

 

[kloving24]

Thank you for replying. Calculated reading should be a output column, so the values should be as shown below. It is basically just averaging till you get to the next reading based off of the dates and readings that you have.

[TABLE="width: 274"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Readings[/TD]
[TD]Calculated Reading[/TD]
[/TR]
[TR]
[TD="align: right"]11/1/2018[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]11/2/2018[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD="align: right"]11/3/2018[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: right"]11/4/2018[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD="align: right"]11/5/2018[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD="align: right"]11/6/2018[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]112.5[/TD]
[/TR]
[TR]
[TD="align: right"]11/7/2018[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD="align: right"]11/8/2018[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]137.5[/TD]
[/TR]
[TR]
[TD="align: right"]11/9/2018[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]150[/TD]
[/TR]
</tbody>[/TABLE]

 

[KolGuyXcel]

Try this! It assumes that the real values will ALWAYS go up (whenever they appear) as we go down the column, along with the following assumptions! The values are rounded up to 2 digits after decimal. If needed to change, change the purple "2" in the formula!

Assumptions (adjust the range according to the mismatch in the assumptions):
The column with the heading Date is Column A.
The column with the heading Reading is Column B, with B2 always having a real value.
The column with the heading Calculated Reading is Column D.
The data is beginning from row 2 and is running upto row 10.

Introduce a helper column in Column C (This could be kept hidden) and any text can be used as the heading. I used Helper.

Formula in column C
=IF(ISERROR(B2),C1,ROW(B2))

Formula in Column D is the following array formula (with Shift+Ctrl+Enter)
=IFERROR(ROUND((D1+((INDEX(B:B,INDEX(ROW(B2:$B$10),MATCH(FALSE,ISERROR(B2:$B$10),0)))-INDEX(B:B,C2))/(INDEX(ROW(B2:$B$10),MATCH(FALSE,ISERROR(B2:$B$10),0))-C2))),2),B2)

 

[bosco_yip]

Or, try this without helper

In C2, copied down :

=IF(A2="","",IF(ISNUMBER(B2),B2,C1+(INDEX(B2:B$10,MATCH(1,INDEX(0+ISNUMBER(B2:B$10),0),0))-INDEX(B$2:B2,MATCH(9^9,B$2:B2)))/(INDEX(A2:A$10,MATCH(1,INDEX(0+ISNUMBER(B2:B$10),0),0))-INDEX(A$2:A2,MATCH(9^9,B$2:B2)))*(A2-A1)))

Regards
Bosco

 

[kloving24]

Thank you so much, Bosco. That formula worked.

I have one more question for you. Is these a way to limit the decimal places?

Thank you again for your help!

Katie

 

[kloving24]

Or, try this without helper

In C2, copied down :

=IF(A2="","",IF(ISNUMBER(B2),B2,C1+(INDEX(B2:B$10,MATCH(1,INDEX(0+ISNUMBER(B2:B$10),0),0))-INDEX(B$2:B2,MATCH(9^9,B$2:B2)))/(INDEX(A2:A$10,MATCH(1,INDEX(0+ISNUMBER(B2:B$10),0),0))-INDEX(A$2:A2,MATCH(9^9,B$2:B2)))*(A2-A1)))

Regards
Bosco

Hi Bosco,

I was wondering if you could help me with one last part?

Sometimes when the data is entered the value is reset because they refill the system with a certain amount. Is there a way to look at the value and average down to that value then reset the value.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Bleach Amount[/TD]
[TD]Reset[/TD]
[/TR]
[TR]
[TD]10-11-2018[/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10-12-2018[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10-13-2018[/TD]
[TD][/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]10-14-2018[/TD]
[TD]34[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to average down to the reset value and then start averaging again after the reset value. Thank you for any help you can offer.