# Can this be done in 1 formula

#### Uzma Shaheen

##### Active Member
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?

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### Uzma Shaheen

##### Active Member
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>

Last edited:

#### Rick Rothstein

##### MrExcel MVP
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

##### Active Member
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

##### Active Member
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

Last edited:

#### Rick Rothstein

##### MrExcel MVP
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

##### Active Member
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)

Last edited:

#### Uzma Shaheen

##### Active Member
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)

Last edited:

#### Uzma Shaheen

##### Active Member
Hi Rick

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

#### Rick Rothstein

##### MrExcel MVP
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?

Replies
7
Views
316
Replies
5
Views
301
Replies
6
Views
242
Replies
2
Views
124
Replies
6
Views
223

1,190,783
Messages
5,982,900
Members
439,805
Latest member
IDarkstarX

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back