Can this be done in 1 formula

[Uzma Shaheen]

Hi

Not sure if this is possible. I was hoping this could be done in 1 formula

I have a range that lists hours that are scheduled every day and have a start figure..

Ie

300.00 (start)

Scheduled
50
50
170
40
50
60

What i need is a formula to do this

If the previous day > than next day then previous take away the next else previous/next and then adding it together when its diving it

Ie in my example

300-50 = 250 (1)
250-50 = 200 (2)
200-170= 30 (3)
30 is less than 40 therefore
30/40 = 0.75

I then need to add the decimal part to the count of days
So the result should show 3.75

Can this be done in 1 formula?

 

[Tetra201]

... Later, I will try to turn it into an array formula.

Here is my single-cell array formula solution (to be entered using Ctrl+Shift+Enter, not just Enter):

=SUM(IF(A2:A100<=($A$1*2-SUBTOTAL(9,OFFSET($A$1,0,0,ROW(A2:A100)-ROW($A$2)+1))),1,IF(($A$1*2-SUBTOTAL(9,OFFSET($A$1,0,0,ROW(A2:A100)-ROW($A$2)+1)))<0,0,($A$1*2-SUBTOTAL(9,OFFSET($A$1,0,0,ROW(A2:A100)-ROW($A$2)+1)))/(A2:A100))))

 

[Uzma Shaheen]

Thank you all