Can this be done in 1 formula

Uzma Shaheen

Active Member
Joined
Nov 10, 2012
Messages
457
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
Joined
Nov 10, 2012
Messages
457
Breaking it down into multiple cells - this is the result it should be (Hoping this can be done in 1 formula)

Data

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

<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:
Upvote 0

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,150
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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)?
 
Upvote 0

Uzma Shaheen

Active Member
Joined
Nov 10, 2012
Messages
457
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))
 
Upvote 0

Uzma Shaheen

Active Member
Joined
Nov 10, 2012
Messages
457
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:
Upvote 0

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,150
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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?
 
Upvote 0

Uzma Shaheen

Active Member
Joined
Nov 10, 2012
Messages
457
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 11
200 1 full day because it is greater than or equal to 11
30 1 full day because it is greater than or equal to 11
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:
Upvote 0

Uzma Shaheen

Active Member
Joined
Nov 10, 2012
Messages
457
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:
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top