Can this be done in 1 formula

Uzma Shaheen

Active Member
Joined
Nov 10, 2012
Messages
484
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
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?
 
All the data (hours) are in column A
the output should be in cell B1
 
Upvote 0

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Hi Rick,

Im getting an error with the formula (syntax error)

I need the end result to be in one cell therefore it should not be dragged down in column B - The result should be in cell B1 or B2
 
Upvote 0
This works if i use these formula

All the data in Column A

Code:
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]300[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]170[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[/TR]
</tbody>[/TABLE]

Formulas
Code:
[TABLE="width: 433"]
<tbody>[TR]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD="align: right"]250[/TD]
[/TR]
[TR]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]0.75[/TD]
[/TR]
</tbody>[/TABLE]

End result im after
Code:
[TABLE="width: 389"]
<tbody>[TR]
[TD="class: xl65, width: 389"]3.75[/TD]
[/TR]
</tbody>[/TABLE]
Formulas used

Code:
In B1 ....=A1
In B2......copied down say B20  =IF(COUNTIF($B$1:B1,"<1")>=1,"",IF(MAX(B1,A2)>A2,B1-A2,B1/A2))
In C1 ...the end result.....  =COUNTIFS($B$2:$B$20,">=1")+LOOKUP(1E+100,$B$2:$B$20)

Now what i would like is to have this end result in C1 to have a formula that gives me the same result in C1 without having to use the formulas used in column B2 copied down so have 1 formula that gives me the result
 
Last edited:
Upvote 0
Okay, give this a try...

B1: =A1
B2: =IFERROR(IF(A2<b1,b1-a2,if(a1>#REF!,"",ROW(B1)-1+B1/A2)),"")

Copy B2 down to the end of your data.


Hi Rick - The syntax on this shows that its not right - i probably am doing something wrong
</b1,b1-a2,if(a1>
 
Upvote 0
Not a single-cell solution, but at least it does not require any additional setup:

Cell B1 ... =SUM(B2:B100)
Cell B2 ... =IFERROR(IF($A2<=($A$1*2-SUM($A$1:$A1)),1,MAX(0,($A$1*2-SUM($A$1:$A1))/$A2)),"") and copy down as needed.

Later, I will try to turn it into an array formula.
 
Upvote 0
Brill - thank you -if you or someone can come up with a single cell solution, i would be amazed to see it as this is a challenge that apparently cant be done so was hoping a guru can prove us wrong and amaze us with a single cell solution
 
Upvote 0
Not a single-cell solution, but at least it does not require any additional setup:

Cell B1 ... =SUM(B2:B100)
Cell B2 ... =IFERROR(IF($A2<=($A$1*2-SUM($A$1:$A1)),1,MAX(0,($A$1*2-SUM($A$1:$A1))/$A2)),"") and copy down as needed.

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

Is there any chance you can break the formula down to why you used *2? ust trying to understand why you set up the formula like you did so i can think more logically like you guys
 
Upvote 0
Hi Rick - The syntax on this shows that its not right - i probably am doing something wrong
</b1,b1-a2,if(a1>
It was not your fault... I screwed the formula up.:oops: Put this formula in cell B1...

=A1

and put this formula in cell B2 and copy it down...

=IFERROR(IF(A2<B1,B1-A2,IF(A1>INDEX(B:B,ROW(B1)-(ROW(B1)>1)),"",ROW(B1)-1+B1/A2)),"")
 
Upvote 0
Thank you Rick but its showing another syntax error with your formula :)

It was not your fault... I screwed the formula up.:oops: Put this formula in cell B1...

=A1

and put this formula in cell B2 and copy it down...

=IFERROR(IF(A2<b1,b1-a2,if(a1>INDEX(B:B,ROW(B1)-(ROW(B1)>1)),"",ROW(B1)-1+B1/A2)),"")

</b1,b1-a2,if(a1>
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top