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]

Is there any chance you can break the formula down to why you used *2?

At "step 1" you would need to subtract SUM($A$1:$A1) from a number and get the original start value. Thus, $A$1*2-SUM($A$1:$A1), that is (A1*2 - A1) --> A1.

Hope this makes sense.

 

[Uzma Shaheen]

At "step 1" you would need to subtract SUM($A$1:$A1) from a number and get the original start value. Thus, $A$1*2-SUM($A$1:$A1), that is (A1*2 - A1) --> A1.

Hope this makes sense.

That makes sense - Thank you

To eliminate the 0s for the Max formula and return blanks, i did another If but im sure this can be shortened

Hopefully there is a 1 cell solution also

=IFERROR(IF($A2<=($A$1*2-SUM($A$1:$A1)),1,IF(MAX(0,($A$1*2-SUM($A$1:$A1))/$A2)=0,"",MAX(0,($A$1*2-SUM($A$1:$A1))/$A2))),"")

 

[Tetra201]

It you do not want to see zeros, here is a modified formula:

=IFERROR(IF($A2<=($A$1*2-SUM($A$1:$A1)),1,IFERROR(1/(1/MAX(0,($A$1*2-SUM($A$1:$A1))/$A2)),"")),"")

 

[Uzma Shaheen]

It you do not want to see zeros, here is a modified formula:

=IFERROR(IF($A2<=($A$1*2-SUM($A$1:$A1)),1,IFERROR(1/(1/MAX(0,($A$1*2-SUM($A$1:$A1))/$A2)),"")),"")

AgainWow

Can you be kind enough to explain this part as i always use an extra if to eliminate the results when its 0

What i've learnt is that if the results are negative and i want to show that as a 0 use the Max(0,???) formula

I have never seen this way where if it is 0 then this changes to blanks without having an extra If so it would be really nice to understand why you sis it this way - thank you again

IFERROR(1/(1/MAX(0,($A$1*2-SUM($A$1:$A4))/$A5)

 

[Eric W]

Here's an array formula to handle it. I used 2 cells, C1 and D1. Theoretically you can combine them into a single formula, but it would be very long.

	A	B	C	D
1	300	3.75	3	3.75
2	50	1
3	50	1
4	170	1
5	40	0.75
6	20
7	30
8	40
9	50

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet20​

Worksheet Formulas Cell Formula B1 =SUM(B2:B100) B2 =IFERROR(IF($A2<=($A$1*2-SUM($A$1:$A1)),1,IFERROR(1/(1/MAX(0,($A$1*2-SUM($A$1:$A1))/$A2)),"")),"") D1 =IF(A1>SUM(A2:A9),COUNTA(A2:A9),C1+(A1-IFERROR(SUM(OFFSET(A2,0,0,C1)),0))/OFFSET(A2,C1,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula C1 {=MIN(IF(SUBTOTAL(9,OFFSET(A2,0,0,ROW(A2:A9)-ROW(A2)+1))>A1,ROW(A2:A9)-ROW(A2)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I used Tetra's formula in B as a reference.

 

[Tetra201]

Here's how it works:

- if X is a non-zero number, 1/(1/X) will return X, and ISERROR will pass it through;
- however, if X equals to zero, 1/X will return #DIV/0!, and 1/(1/X) will also return #DIV/0!. This is trapped by ISERROR and returned as "".

 

[Uzma Shaheen]

WOWWWWWWWWWWWWWWWWWWWWWWWWWWWWW

Amazing - Can you please please help me understand the formula especially the use of Min, Sum, subtotal and offset in both formulas in C1 and D1

Please Eric if you can be kind enough to explain this

I have turned it into 1 formula just so i go back and say it could be done to my colleagues but no doubt they will ask me to break down the formula especially the parts i mentioned so would be greatfull if you could

Thank you once again

Here is your combined beautiful formula

=IF(A1>SUM(A2:A9),COUNTA(A2:A9),MIN(IF(SUBTOTAL(9,OFFSET(A2,0,0,ROW(A2:A9)-ROW(A2)+1))>A1,ROW(A2:A9)-ROW(A2)))+(A1-IFERROR(SUM(OFFSET(A2,0,0,MIN(IF(SUBTOTAL(9,OFFSET(A2,0,0,ROW(A2:A9)-ROW(A2)+1))>A1,ROW(A2:A9)-ROW(A2))))),0))/OFFSET(A2,MIN(IF(SUBTOTAL(9,OFFSET(A2,0,0,ROW(A2:A9)-ROW(A2)+1))>A1,ROW(A2:A9)-ROW(A2))),0))

 

[Anand Sharma]

you can do it as below

300	Start	300	Put 300 Manually in First
50	Schedule	250	IF(C1>A2,C1-A2,A2/C1)
50	Schedule	200	IF(C2>A3,C2-A3,A3/C2)
170	Schedule	30
40	Schedule	0.75
20	Schedule	0.0375

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Thank you Rick but its showing another syntax error with your formula

Ah, I see the problem... this forum's comment processor interprets "less than" symbols as the start of an HTML tag unless it is first followed by a space. So, I pasted in my formula which has a "less than" symbol and the forum's comment processor screwed it up. Here is my formula for cell B2 with the logic reversed so it uses a "greater than" symbol instead.

=IFERROR(IF(B1>A2,B1-A2,IF(A1>INDEX(B:B,ROW(B1)-(ROW(B1)>1)),"",ROW(B1)-1+B1/A2)),"")

Just noting... my formula is not to be array-entered, simply commit it with a normal press of the Enter Key.

 

[Eric W]

OFFSET creates a range that can be used in other formulas. The first parameter is a cell reference, the next parameter is how many rows down to start, then how many columns over to start, then how many rows to include in the range, then how many columns.

In the C1 formula, the ROW(A2:A9)-ROW(A2)+1 part generates an array of counters {1,2,3,4,5,6,7,8}. Then that is given to the # of rows parameter of OFFSET, resulting in A2, A2:A3, A2:A4, etc. As a rule, you can't use OFFSET in an array formula, but it works in SUBTOTAL, which is why I used that instead of just SUM. The sums of those ranges are {50,100,270,310,330,360,400,450}. Then the IF part compares each of those values with A1 returning TRUE if the sum is greater than A1, returning this array {FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE}. Then if TRUE, we get the result of this array {0,1,2,3,4,5,6,7}, getting {FALSE,FALSE,FALSE,3,4,5,6,7}. Then MIN gives us 3, which is the last row where the sum is under 300.

Given that value in C1:

=IF(A1>SUM(A2:A9),COUNTA(A2:A9),C1+(A1-IFERROR(SUM(OFFSET(A2,0,0,C1)),0))/OFFSET(A2,C1,0))

The part in red uses the OFFSET as described above to get the sum of the first 3 rows. I put IFERROR around it in case A2 > A1 to return 0. Then the part between green parentheses returns the difference (300-270) = 30. The OFFSET in pink returns the next value in the column or 40. Then the whole thing is added to C1. The part in brown is to handle the case where A1 is greater than the sum of everything in A2:A9. If so, it just returns the number of values in A2:A9.

That's the quick rundown. Hope it helps!