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?

 

[Uzma Shaheen]

Breaking it down into multiple cells - this is the result it should be (Hoping this can be done in 1 formula)

Data

300	Start
50	Schedule
50	Schedule
170	Schedule
40	Schedule
20	Schedule
30	Schedule
40	Schedule
50	Schedule

<tbody>
</tbody>

Formula to do this

300

250

200

30

0.75

<tbody>
</tbody>

=C1

=IF(MAX(C1,A2)>A2,C1-A2,C1/A2) dragged down (stop until i end up dividing)

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

3 full days +0.75

<tbody>
</tbody>

End result

3.75

<tbody>
</tbody>

 

[Rick Rothstein]

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

What happens next (with the 50 and 60 that comes after the 40)?

 

[Uzma Shaheen]

This seems to turn the formula off when it gets do the division part however still need a way of doing it all in 1 cell and in that cell have the formula to add the full days (count of hours) that are >=1 to the decimal part (proportion of that day)

=C1
=IF(COUNTIF($C$1:C1,"<1")>=1,"",IF(MAX(C1,A2)>A2,C1-A2,C1/A2))

 

[Uzma Shaheen]

Hi Rick - they are the schedules - they dont need to be included once the result of the calculation turns out to be lower than the schedules

ie

200 - 170 = 30
30 > 40 therefore 30/40 (From this point onwards - i dont need to carry on therefore can return blank) So when its down to the proportion of a day by dividing, i dont need to look at the rest of the scheduled dates once i have divided which tells me that i am down to the proportion of a day which will then be added on to the count of the full days

 

[Rick Rothstein]

Hi Rick - they are the schedules - they dont need to be included once the result of the calculation turns out to be lower than the schedules

ie

200 - 170 = 30
30 > 40 therefore 30/40 (From this point onwards - i dont need to carry on therefore can return blank) So when its down to the proportion of a day by dividing, i dont need to look at the rest of the scheduled dates once i have divided which tells me that i am down to the proportion of a day which will then be added on to the count of the full days

Okay, but I think we need a clarification as to what you actually want. What you wrote in Message #4 seems to indicate you only want one formula in cell C1 that is not copied down, is that correct? If so, does that mean you want the formula to update as each new entry is added to Column A. Again, if so, exactly what should be shown for each entry... the remaining hours or the day count?

 

[Uzma Shaheen]

Hi Rick

C1 - is the start cell im looking from - If this can be incorporated into the formula that would be fantastic

The reason i had C1 not copied down because i did not know how to show the formulas i was coming up with so that you guys can see what i was trying to do

If hours are added to column A - I would need to show the day count and any proportion of that day added on to the day count

So without any formulas - this is how id like the results to show based on the data in Column A

Raw Data

300
50
50
170
40
20
30
40
50
60
70
80
90
10
10
20
50

<tbody>
</tbody>

End Result

250	1 full day because it is greater than or equal to 1	1
200	1 full day because it is greater than or equal to 1	1
30	1 full day because it is greater than or equal to 1	1
0.75	I divided at this point therefore result shows that im calculating a proportion of that day	0.75
		3.75

<tbody>
</tbody>

I'm so so sorry if i'm not being clear (I'm trying)

The goal is to keep taking away the result of the formula from the next schedule until it turns out that the result is > than the next schedule therefore it needs to be divided (at that point i dont need to look at any schedules or compare going forward)

The calculation when divided needs to be added on to the count of full days

start - next (if it is start of the cell or the result of the previous calculation is > than the next scheduled hours then divide the next schedule hours else take away the next scheduled hours

If i have already divided then add this to the count of how many full days from previous calculation (ie whole numbers)

 

[Uzma Shaheen]

So say the start value was
300 and the next scheduled value was 400

300
400
No need to carry on looking at the next schedule
500
600


I would do 300/400 = 0.75 therefore my end result would be that

I dont need to take away if (the start cell or the result of the formula if i had taken away previously is > than next scheduled hours)

 

[Uzma Shaheen]

Hi Rick

I hope I explained myself better - appogies if i still have not been more clear

 

[Rick Rothstein]

Hi Rick

I hope I explained myself better - appogies if i still have not been more clear

What column is your raw data in and what column do you want the output to go to?