Function to only perform up to last value

[Drivingman]

HI All
Hoping you can help me sort this out. I thought I had it but not working how I want it.

I have a formula that I am changing daily to include one extra cell in the formula.

=((SUM(Daily!BL10:bt10)-SUM(Daily!BL4:bt4))/SUM(Daily!BL4:bt4))

The range is all the way up to CP4 or CP10.

The blank values are what is in row 10. So currently BL10-BT10 all have values in, and tomorrow etc extra cells will have values in.
I cant just use the full range as next weeks values are already in row 4, so the result is lower than actual.

I had thought the following would work, but alas not.

=((SUMIF(Daily!BL10:CP10,"<>",Daily!BL10:CP10)-SUMIF(Daily!BL10:CP10,"<>",Daily!BL4:CP4))/SUMIF(Daily!BL10:CP10,"<>",Daily!BL4:CP4))


Thanks for your help.

 

[oldbrewer]

why BL10:BT10 why not BL10:EZ10 ?

 

[Drivingman]

BL is the start of the month and BT is the cell we are currently in. The month will end on CP so don’t need to go past that column.
Anything that will only do the function up to the last empty cell on row 4.

 

[Peter_SSs]

Assuming row 10 gets filled from the left with no blanks between the numbers (only blanks to the right of the numbers), then try this

=(SUM(BL10:CP10)-SUM(BL4:INDEX(BL4:CP4,COUNT(BL10:CP10))))/SUM(BL4:INDEX(BL4:CP4,COUNT(BL10:CP10)))

 

[oldbrewer]

are the column headers DATES as you say "the month will end on CP"

 

[Drivingman]

Yes the column headers are dates.

 

[Peter_SSs]

.. and the formula I suggested?

If it does need to go on a different sheet and may need to be copied down referencing different rows than row 10 but always referencing row 4, it would be:

=(SUM(Daily!BL10:CP10)-SUM(Daily!BL$4:INDEX(Daily!BL$4:CP$4,COUNT(Daily!BL10:CP10))))/SUM(Daily!BL$4:INDEX(Daily!BL$4:CP$4,COUNT(Daily!BL10:CP10)))

 

[oldbrewer]

col B

row 3

01/06/2018

02/06/2018

03/06/2018

04/06/2018

05/06/2018

06/06/2018

07/06/2018

08/06/2018

09/06/2018

10/06/2018

11/06/2018

12/06/2018

13/06/2018

14/06/2018

15/06/2018

16/06/2018

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

desired start date

05/06/2018

desired end date

14/06/2018

total

125

=SUMPRODUCT(($B$3:$Q$3>=$H$13)*($B$3:$Q$3<=$H$14)*($B$4:$Q$4))

desired start date

05/06/2018

desired end date

newest date

162

=SUMPRODUCT(($B$3:$CZ$3>=$H$23)*($B$3:$CZ$3<=MAX($B$3:$CZ$3))*($B$4:$CZ$4))

<colgroup><col><col><col span="5"><col><col span="9"><col></colgroup><tbody>
</tbody>

 

[Drivingman]

Thank you Peter_SSs, that works a dream, and simple enough I can amend for future months too.

 

[Peter_SSs]

Thank you Peter_SSs, that works a dream, and simple enough I can amend for future months too.

You are welcome.
But if I understand correctly, you shouldn't need to amend anything in future months. As more values are added in row 10, the formula will automatically include those extra columns.