The other day while I was in lift (elevator), it made an alarm like sound and won’t close the doors. Turns out there are one too many people in the lift for it to operate safely. As soon as a couple of people volunteered and stepped out, it started fighting gravity and took us upstairs. That brings us to the problem at hand. Elevator problem.
Let’s say you have a bunch of people and their weights in a spreadsheet like this.
Each row contains some people and their weights. Assume that lift (elevator) can take up to 20 people and total of 1,400 kilos. Your mission, if you choose to accept is this:
- Write a formula / Power Query thingie / VBA / haiku to figure out if the lift should go or stay.
Go… (or may be stay if the load is too heavy or too many)
Lift / Elevator conditions explained:
In order for the lift to move, it needs to satisfy both conditions:
- Total number of people is under or equal to 20
- Total weight is under or equal to 1,400
Download sample data – elevator problem workbook
Click here to download sample data for this problem. Write your formulas / PQ / other solutions in the workbook. Once you crack it, comeback and post your solution in the comments.
Note about using < or > symbols in comments. Our blog commenting system digests angle brackets. So just use LT and GT instead of < or > symbols and you are good to go.
Want more problems? We got some for you:
No one likes problems. But if you are that rare snowflake who likes challenges, puzzles and problems, then check out Excel Homework page. You will be rewarded handsomely.
192 Responses
Very good post. Keep it up.
Thanks for sharing sample data.
I guess the simple formula below should do the trick.
=IF((OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400)),”NO”,”GO”)
Let me know…
Thx.
+1.
BUT I would change the 1400 to the cell $AA$6 and 20 to $AA$5 for good practice. The same for the other cells.
What’s the trick?!
=IF(AND(COUNT($C4:$X4) LT= $AA$5;SUM($C4:$X4) LT= $AA$6);”Yes”;”No”)
=IF(AND(COUNT(C4:X4)LT 21,SUM(C4:X4) LT 1400),”Go”,”Stay”)
Formula at cell No. “B4″
=IF(COUNT(C4:X4)>$AA$5,”STOP”,IF(SUM(C4:X4)>$AA$6,”STOP”,”Go”))
Copy this upto “B13”.
Regards
Rajesh
=IF(AND(COUNT(C4:X4)<=AA$5,SUM(C4:X4)<=AA$6),"Go","Stay")
=IF(AND(COUNT(C4:X4)<=20,SUM(C4:X4)<=1400),"Go","Stay")
=IF(COUNT(C4:Y4)<=AA$5*(SUM(C4:Y4)<=AA$6),"YES","")
=IF(AND((COUNT(C4:X4)<=$AA$5),(SUM(C4:X4)<=$AA$6)),"Yes","No")
=IF((COUNT(C4:X4)<=$AA$5)*(SUM(C4:X4)<=$AA$6),"Go","Stop")
=IF(OR(COUNT(C4:X4)RT$AA$5,(SUM(C4:X4)RT$AA$6)),”STOP”,”GO”)
Haiku version:
Elevator goes?
twenty people and less than
1 point 4 kilo
VBA version:
Sub CanItGo()
Dim rowCount As Integer
Dim weightTotal As Integer
Dim peopleTotal As Integer
For rowCount = 4 To 13
weightTotal = WorksheetFunction.Sum(Range(“C” & rowCount & “:X” & rowCount))
peopleTotal = WorksheetFunction.CountA(Range(“C” & rowCount & “:x” & rowCount))
If weightTotal GT Range(“AA6”).Value Then Range(“B” & rowCount).Value = “too heavy”
If peopleTotal GT Range(“AA5”).Value Then Range(“B” & rowCount).Value = “too many”
If weightTotal GT Range(“AA6”).Value And peopleTotal GT Range(“AA5”).Value Then Range(“B” & rowCount).Value = “too many and too heavy”
Next rowCount
End Sub
I think you can go even simpler — assuming the data is input/collectect correctly you don’t even need to count the columns. Just check if Wx is not empty. Additionally you only need to sum up the first 20 columns as you will get a STOP condition if a 21st element even appears.
=IF(OR($W3 GT 0,SUM($C3:$V3) GT 1400),”STOP”,”GO”)
=IF(NOT($W4)*(SUM(C4:X4)<=$AA$6),"Go","Stop")
=IF(AND(SUM(C4:X4)<=1400,COUNT(C4:X4)<=20),"Yes","No")
=IF(OR(SUM(C5:X5)GT1400,COUNT(C5:X5)GT20),”NO”,”YES”)
=IF(OR(SUM(C4:X4)>$AA$6;COUNT(C4:X4)>$AA$5);”Stop”;”GO”)
=IF(AND((COUNT(C4:X4)<=$AA$5),(SUM(C4:X4)<=$AA$6)),"Go","Stay")
=IF(OR(COUNT(C4:X4)>$AA$5,SUM(C4:X4)>$AA$6),”Stay”,”Go”)
“=IF(OR(SUM(C4:X4)>$AA$6;COUNT(C4:X4)>$AA$5);”Stop”;”GO”)”
=IF(OR(SUM(C4:X4)GT1400,COUNT(C4:X4)GT20),”NO”,”YES”)
=IF(AND((COUNT(C4:X4)<=$AA$5),(SUM(C4:X4)<=$AA$6)),"Go","Stay")
Copy down to remaining rows.
Made exact same formula. It’s short and sweet.
Hello
Can you help me with a Excel template for financial projections for 5 yearsfor a start up project in India ,showing profit and loss , balance sheet and cash flow
Regards
Vijayan
{=AND(COUNTA(C4:X4)<=AA$5,SUM(–C4:X4)<=AA$6)}
– entered as an array formula, just in case some people try to sneak their weights in as text!
=IF(AND(COUNTIFS(C4:X4,”>0″)<=20,SUM(C4:X4)<1400)*1=1,"GO","NO GO")
OK I started out by using this formula which seemed to me to be the shortest and most efficient:
=IF(W4=0,IF(SUM(C4:V4)<1401,"GO","NO GO"),"NO GO")
It was not doing repetitive counting and tested only with LT. The summing does not have to include X4
The following solution has the advantage of being easily adjustable for different capacity elevators.
=IF(COUNT(C4:X4)<=$AA$5,IF(SUM(C4:X4)<=$AA$6,"GO","NO GO"),"NO GO")
With a small database such as this the processing efficiency comparison would make essentially no difference.
But wait – – – this may be used thousands of times a day. Do we not have to be careful these days and think about efficiency in our coding?
I am 86 years old and remember the old days when we did.
I may be missing something, but in B4 I entered:
=IF(AND(COUNT(C4:X4)LT21,SUM(C4:X4)LT1401),”Yes”,”No”)
{=IF(OR(SUM(C4:X4)>$AA$6;COUNT(C4:X4)>$AA$5);”Stop”;”Go”)}
=IF(SUM(C4:X4)<=1400,"Lift can move","")
I entered this formula:
=IF(AND(SUM($C4:$X4)LT=1400;COUNT($C4:$X4)LT=20);”Can”;”Can’t”)
I will use this formula =IF(AND(SUM(C4:X4)<=$AA$6,COUNT(C4:X4)<=20),"Go","Not Go")
=IF(AND(COUNT(C4:X4)<=20,SUM(C4:X4)<=1400),"Go","Stay")
Short and sweet:
=IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),”No”,”Yes”)
=IF(AND(COUNT(C4:X4)<=AA$5,SUM(C4:X4)<=AA$6),"Go","Stay")
It’s not often I read these and feel like I can do them. Thanks for throwing in an easy one. 🙂
Here’s my formula:
=IF(AND(COUNT(C4:X4)LT=AA$5,SUM(C4:X4)LTAA$6),”Go”,”Stay”)
This seems to work:
=IF(AND(COUNTA(C4:X4)<$AA$5+1,SUM(C4:X4)<$AA$6+1),"Good to go","PROBLEM")
=IF(AND(COUNTA(C4:X4)<=$AC$5,SUM(C4:X4)<=$AC$6),"GO","STAY")
=IF((–(COUNT(C4:X4)<=$AA$5)+–(SUM(C4:X4)=2, “YES”, “NO”)
=IF(AND(COUNT(C4:X4)<21,SUM(C4:X4)<1401),"YES","NO")
…or this…
=IF(AND(COUNT(C4:X4)<$AA$5,SUM(C4:X4)<$AA$6),"YES","NO")
=IF(AND((COUNTA(C4:X4)<=20),(SUM(C4:X4)<=1400)),"Y","N")
=IF(OR(COUNTA(C4:X4)>$AA$5,SUM(C4:X4)>$AA$6),”No”,”OK”)
=IF(OR(COUNT(C4:X4)>$AA$5,SUM(C4:X4)>$AA$6),”No”,”OK”)
=IF(AND((COUNTA(C3:X3)<=20), (SUM(C4:X4)<=1400)),"GO","NO GO")"
=IF(AND((COUNTA(C4:X4)<=20), (SUM(C4:X4)<=1400)),"GO","NO GO")
It’s almost the same formula, I just named the cells to make reading easier:
maxPeople -> cell AA5
maxWeight -> cell AA6
Formula in B4:
=IF(AND(COUNT(C4:X4)<=maxPeople,SUM(C4:X4)<=maxWeight),"YES","NO")
=IF(AND(COUNT(C4:X4)<=20,SUM(C4:X4)<=1400),"go","stay")
please correct if I am wrong
in portuguese:
=SE(E(SOMA(E4:Z4)<=$AC$6;CONT.VALORES(E4:Z4)<=$AC$5);"Y";"N")
=IF(AND(COUNTA(C4:X4)LT=AA$5,SUM(C4:X4)LT=AA$6),”GO”,”STAY”)
IF((SUM(OFFSET(C4,,,1,COUNTA(C4:Z4)))<1401)*COUNTA(C4:Z4)<21,"go","no go")
* counta , should be count.
(not sure why my initial response didn’t post … I’m going to assume the Interweb ate it)
Haiku version:
Elevator goes?
Less than twenty folks and less
than one point four k
VBA version:
Sub CanItGo()
Dim rowCount As Integer
Dim peopleCount As Integer
Dim weightCount As Integer
For rowCount = 4 To 13
weightCount = WorksheetFunction.Sum(Range(“C” & rowCount & “:x” & rowCount))
peopleCount = WorksheetFunction.CountA(Range(“C” & rowCount & “:x” & rowCount))
If weightCount GT Range(“AA6”).Value Then Range(“B” & rowCount).Value = “Too heavy”
If peopleCount GT Range(“AA5”).Value Then Range(“B” & rowCount).Value = “Too Many”
If weightCount GT Range(“AA6”).Value And peopleCount GT Range(“AA5”).Value Then Range(“B” & rowCount).Value = “Too many and too heavy”
Next rowCount
End Sub
OK, a bit left field
Select C4:X13, Conditional Formatting, New Rule, Using Formula and enter the following
=AND(C4″”,SUM($C4:C4)<=$AA$6,COUNT($C4:C4)<=$AA$5)
then format to your favourite colour OK, OK, OK and BOOM
This will highlight those that can stay so that the lift can go in every event, with those not highlighted having to leave.
First off, I changed B3 to read “Alarm?”; then used the formula:
=OR(COUNTA(C4:X4)GT$AA$5,SUM(C4:X4)GT$AA$6)
if there’s a prize for the longest, least clever way to do it, this should do the trick:
=IF(IF(AND(IF(COUNT(C4:X4)<=20,1,0)=1,IF(SUM(C4:X4)<=1400,1,0)=1),1,0)=1,"Go","Stay")
I offer this … =IF(AND(COUNT(C4:X4)<=20,SUM(C4:X4)<=1400),"Go","Stay!")&" … "&COUNT(C4:X4)&" People "&SUM(C4:X4)&" Total Weight"
Which tests for No of people <= 20 and combined weight of <=1400
Then I added by concatenation, outside the scope of the exercise, information saying how many people were in the lift and their combined weight.
Finally, I used conditional formatting on the output range to show green text and fill if the answer was Go and red text and fill if the answer was stay.
By the way, I know AI etc is here but how many lifts can count how many people are in it?!
Of course, I should have offered this
=IF(AND(COUNT(C4:X4)<=AA$5,SUM(C4:X4)<=AA$6),"Go","Stay!")&" … "&COUNT(C4:X4)&" People "&SUM(C4:X4)&" Total Weight"
hard coding of the 20 and 1400 is bad practice … one day, when they strengthen the lift and change 20 to 23 and 1400 to 1500, my formula would have failed.
my solution:
=IF(AND((COUNT(C4:X4)<21),(SUM(C4:X4)<1401)),"GO","STAY")
=IF(AND(COUNTIF(C4:X4,”>0″)<=$AA$5,SUMIF(C4:X4,"<="&$AA$6)),"GO","STAY")
=IF(AND(COUNT(C4:X4)<=$AA$5,SUM(C4:X4)<=$AA$6),"YES","NO")
=IF(AND(SUM(C4:X4)<=$AA$6,COUNTA(C4:X4)<=$AA$5),"CAN GO","CAN'T GO")
=IF(AND(COUNT(C4:X4)<=$AA$5,SUM(C4:X4)<=$AA$6),"Yes","No")
Here is my Power Query solution with comments above each line.
let
Source = Excel.CurrentWorkbook(){[Name=”Table3″]}[Content],
// demoted headers
#”Demoted Headers” = Table.DemoteHeaders(Source),
// removed first row
#”Removed Top Rows” = Table.Skip(#”Demoted Headers”,1),
// removed Can_Go column; recreate later
#”Removed Columns” = Table.RemoveColumns(#”Removed Top Rows”,{“Column1″}),
// create new column; sum entire row without using column headings
#”Inserted Sum” = Table.AddColumn(#”Removed Columns”, “Total Weight”, each List.Sum(List.Select(Record.ToList(_), each _ is number))),
// create new column; count columns that have number without using column headings; subtract 1 due to sum column
#”Inserted Count” = Table.AddColumn(#”Inserted Sum”, “Total People”, each List.Count(List.Select(Record.ToList(_), each _ is number))-1),
// create new column; recreate Can_Go column
#”Added Custom” = Table.AddColumn(#”Inserted Count”, “Can Go?”, each if [Total Weight]<= 1400 and [Total People]<= 20 then "Go" else "Stay"),
// create new column; group number using index
#"Added Index" = Table.AddIndexColumn(#"Added Custom", "Group#", 1, 1),
// rearrange and remove columns
#"Removed Other Columns" = Table.SelectColumns(#"Added Index",{"Can Go?", "Group#", "Total People", "Total Weight"}),
// fix column types
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Can Go?", type text}, {"Group#", Int64.Type}, {"Total People", Int64.Type}, {"Total Weight", Int64.Type}})
in
#"Changed Type"
=IF(AND(COUNTA($C4:$X4)<=20,SUM($C4:$X4)<1400),"Lift is Moving","Pls Volunteer, Lift is Not moving")
used this formula.. as 2 criteria to be matched..
Single if statement with AND condition solved the purpose.. Pls let us know even better answer, I like to hear from you Chandoo.
=IF(AND(COUNT(C4:X4)<=20,SUM(C4:X4)<=1400),"Yes","No")
If((And(Count(C4:X4) <=$AA$5,SUM(C4:X4)<=$AA$6)),"Yes","No")
I’ll try haiku..
I look around me.
“Lady, how much do you weigh?”
Slap to the face. Ding!
haha… always a bad idea.
never ask a man’s
wealth or a woman’s weight
to save your cheeks
My solution is given below
=IF(AND(($AA$5-COUNTA($C4:$X4) >=0),($AA$6-SUM($C4:$X4) >=0)),”YES”,”NO”)
and I’ll take a stab at the M code too:
let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
#”Removed Columns” = Table.RemoveColumns(Source,{“Can go?”}),
#”Added Index” = Table.AddIndexColumn(#”Removed Columns”, “Index”, 1, 1),
#”Unpivoted Other Columns” = Table.UnpivotOtherColumns(#”Added Index”, {“Weights”, “Index”}, “Attribute”, “Value”),
#”Grouped Rows” = Table.Group(#”Unpivoted Other Columns”, {“Weights”, “Index”}, {{“Count of People”, each Table.RowCount(_), type number}, {“Sum of Weight”, each List.Sum([Weights]), type number}}),
#”Added Custom” = Table.AddColumn(#”Grouped Rows”, “Can go?”, each if [Count of People] <= 20 and [Weights] <= 1400 then "Go" else "Stay"),
#"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},#"Added Custom",{"Index"},"Added Custom",JoinKind.LeftOuter),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Can go?"}, {"Can go?"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Added Custom",{"Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Can go?"} & List.RemoveItems(Table.ColumnNames(#"Removed Columns1"),{"Can go?"}))
in
#"Reordered Columns"
oh hang on. I had unpivoted the first weight column as well. fixed:
let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
#”Removed Columns” = Table.RemoveColumns(Source,{“Can go?”}),
#”Added Index” = Table.AddIndexColumn(#”Removed Columns”, “Index”, 1, 1),
#”Unpivoted Other Columns” = Table.UnpivotOtherColumns(#”Added Index”, {“Index”}, “Attribute”, “Value”),
#”Grouped Rows” = Table.Group(#”Unpivoted Other Columns”, {“Index”}, {{“Count of People”, each Table.RowCount(_), type number}, {“Sum of Weight”, each List.Sum([Value]), type number}}),
#”Added Custom” = Table.AddColumn(#”Grouped Rows”, “Can go?”, each if [Count of People] <= 20 and [Sum of Weight] <= 1400 then "Go" else "Stay"),
#"Merged Queries" = Table.NestedJoin(#"Added Index",{"Index"},#"Added Custom",{"Index"},"Added Custom",JoinKind.LeftOuter),
#"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Can go?"}, {"Can go?"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded Added Custom",{"Index"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Can go?"} & List.RemoveItems(Table.ColumnNames(#"Removed Columns1"),{"Can go?"}))
in
#"Reordered Columns"
Please see the Amswer
=IF(OR(COUNT(C4:X4)GT20,SUM(C4:X4)GT1400),”Over Weight”,”No Problem”)
I liked that Ghazanfar
=IF(+COUNT(C4:X4)>20,”Stay”,IF(SUM(C4:X4)<=1400,"Go","Stay"))
+IF(COUNT(C8:X8)LT21,IF(SUM(C8:X8)LT1400,”Go”,”Dontgo”),”Dontgo”)
=IF(AND(COUNTA(C4:X4)<$AA$5+1,SUM(C4:X4)<$AA$6+1),"GO","OVERLOAD")
Copy this formula and you are good to go.
Please see the answer in Below
=IF(OR(COUNT(C4:X4)GT20,SUM(C4:X4)GT1400),”Over Weight”,”No Problem”)
=IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),”Over Weight”,”No Problem”)
=IF(AND(COUNTA(C4:X4)<$AA$5+1,SUM(C4:X4)<$AA$6+1),"GO","OVERLOAD")
Copy this formula then we are good to go.
Please See the Solution.
=IF(OR(COUNT(C4:X4)GT20,SUM(C4:X4)GT1400),”Over Weight”,”No Problem”)
I think this is the shortest so far
=IF(MAX(W4*1000,SUM(C4:V4))<1401,"Go","Stop")
I can't help thinking there must be some cunning back door solution that we're all missing.
I guess, since lifts have no way of knowing the number of people, the simplest solution is just to check the total weight.
=IF(AND(COUNT(C4:X4)<=$AA$5,SUM(C4:X4)<=$AA$6),"Go","Stop")
I use this formula to solve the Problem
=IF(OR(COUNT(C4:X4)>=20,SUM(C4:X4)>1400),”CAN’T GO”,”CAN GO”)
Please See My Answer Below:-
=IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),”Over Weight”,”No Problem”)
=IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),”Over Weight”,”No Problem”)
Sub Elevator_Problem()
Range(“B4:B13”).Select
Selection.FormulaR1C1 = _
“=IF(AND(COUNT(RC[1]:RC[22])<=R5C27,SUM(RC[1]:RC[22])<=R6C27),""GO"",""STOP"")"
Range("A1").Select
End Sub
=IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),”Over Weight”,”No Problem”)
My Answer Is :-
=IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),”Over Weight”,”No Problem”)
=IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),”Over Weight”,”No Problem”)
=IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),”Over Weight”,”No Problem”)
=IF(OR(COUNTA(C4:X4)>$AB$5),”Kidar Vadi Janda ae”,IF(COUNTA(C4:X4)=$AB$5,”Massa hi bacheya haan”,IF(SUM(C4:X4)>$AB$6,”Marr gya ee oa”,”ok”)))
for easy understood i create Working Note
i add
total count in Y column
total weight in z column
and my formula is
=AND(Z4<1400,Y4<20)
‘=+IF(AND(IF(COUNT(F4:AA4)<=AD5,1,0),IF(SUM(F4:AA4)<=AD6,1,0)),1,0)
=IF(OR(COUNT(C4:X4)>$AA$5,SUM(C4:X4)>$AA$6),”Stop”,”Go”)
=IF(COUNT(C4:X4)>$AA$5,”Stay”,IF(SUM(C4:X4)>$AA$6,”Stay”,”Go”))
=IF(AND(SUM(C4:X4)> 1400,COUNT(C4:X4)<21),"UP","Down")
Looking for a more efficient solution.
=IF(AND(COUNT(C4:X4)<=20,SUM(C4:X4)<=1400),"Go","Stop")
=IF(OR(COUNT(C4:X4) GT 20,SUM(C4:X4) GT 1400),”Don’t go”,”GO”)
Hi,
=IF(IF(SUM(C4:X4)>$AA$6,1,0)+IF(COUNTA(C4:X4)>$AA$5,1,0)=2,”Stay”,”Go”)
Gaz
=IF(AND((COUNT(C4:X4)<=$AA$5);(SUM(C4:X4)<=$AA$6));"Yes";"No")
and conditional formatting (Green=Yes, Red=No) more easy to read a large table
=IF(OR(COUNT(C4:X4)>20,SUM( C4:X4)>1400),”can’t go”,”Can go”)
=IF(OR([@SumWeight]>=MaxWeight,[@CountPeople]>=MaxPeople,[@WeightErrorCheck]=”TRUE”),”Stay”,”Go”)
Where:
PeopleWeight is the name of the converted range to Table. This table has 3 additional columns added to avoid having one long IF formula and split the calculations into more smaller steps (useful when there will be a new person checking this in several years).
@SumWeight is =SUM(PeopleWeight[@[W1]:[W22]])
@CountPeople is =COUNT(PeopleWeight[@[W1]:[W22]])
@WeightErrorCheck is =IF([@SumWeight]<0,"TRUE","FALSE")
MaxWeight and MaxPeople are cell names of your provided conditions.
=IF(AND(COUNTA(C4:X4)LT$AA$5;SUM(C4:X4)LT$AA$6);”Yes”;”No”)
In conclusion out of 10 elevator trips 6 is a GO and 4 STAY
=IFERROR(IF(AND(COUNTA(C4:X4)<=$AA$5,SUM(C4:X4)<=$AA$6),"GO","STAY"),"")
Above is the formula I used and this gives me the right criteria
Another is quite a long way where I added the total weight and counted the number of individuals in each trip. I then used the nested IF statement to check the criteria to see if it fitted or not
Any feedbacks is highly appreciated
=+IF(OR(COUNT(C4:X4)>$AA$5,SUM(C4:X4)>$AA$6),”Stop”,”Go”)
=IF(OR(SUM(C4:X4)>$AA$6,COUNTA(C4:X4)>$AA$5),”Stop”,”Go”)
=SI(Y(SUMA(C4:X4)<=1400;CONTAR(C4;X4)<=20);"YES";"NO")
=IF(AND(COUNT($C4:$X4)<=$AA$5;SUM($C4:$X4)<=$AA$6);"Go";"Stay")
Please see my answer
=IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),”Over Weight”,”No Problem”)
=IF(OR(COUNT(C4:X4)>AA$5,SUM(C4:X4)>AA$6),”Can’t Go”, “Can Go”)
right, I’ve got tired of seeing the same solutions here
please, if you’ve nothing original to offer then just read, enjoy and don’t feel the need to “metoo”
having said that, I’ve tried to find the shortest solution to the problem as it stands (in terms of typing – it might have various shortcomings, but it’s a more interesting challenge):
=W4+(SUM(C4:V4)%>14)
only 20 characters; if the result’s 0, then it’s ok to go
loved the haiku
This breaks in rows 8 and 13. Don’t forget the table extends to column X
How so?
if there are entries in column W and X then it has already failed due to too many occupants
Your formula does work but I would expect to see 0 or 1 as outputs rather than 0 and 1 or 62 or 65 as happens in your case. Still, it does work at that level. I know there is no rule in this case to say you did anything wrong but I think modelling best practice is against you.
My point about using columns W and X is that I can see no mechanism in Chandoo’s case that says cells cannot be left blank, even by accident. Moreover, whilst you have caught the >20 and >1400 rule breaks, by essentially ignoring everything to the right of column V, you might not be catching all data … for model review and revision/auditing and so on
hence my caveat about shortcomings
see my earlier entry for a “better” solution
I was just trying to make this more of a challenge by minimising the formula size/computations
While this works OK
You could silence your critics;
Wrap it all with NOT()
~~~
=NOT(W4+(SUM(C4:V4)%>14))
thanks for your comment
I refer you to above
re my caveat
‘=IF(AND(COUNT($C4:$X4)<=$AA$5,SUM($C4:$X4)<=$AA$6),"GO","STOP")
=IF(COUNT(C4:X4)>=$AA$5,”Stop”,IF(SUM(C4:X4)>=$AA$6,”STOP”,”GO”))
One note and one suggestion…
My note: None of the sets in your original data resulted in a group that was under on weight but over on people, so I personally reduced some numbers in the last row to create that scenario.
My suggestion: You typically throw in a challenge question when you give a relatively simple homework assignment, so how about something like writing a single formula to return the number of groups that would receive a Yes/Go response?
Hi David… Good ones. I keep the challenge posts “gettable” to encourage discussion and creativity. For really hard challenges, check out either formula forensics or challenges pages.
https://chandoo.org/wp/formula-forensics-homepage/
https://chandoo.org/wp/category/excel-challenges/
And yes, of course, how would you write a single formula to count number of “go”s in the data?
Array entered…
{=SUMPRODUCT((MMULT(SIGN(C4:X13),TRANSPOSE(SIGN(COLUMN(C3:X3))))<=AA5)*(MMULT(1*C4:X13,TRANSPOSE(SIGN(COLUMN(C3:X3))))<=AA6))}
David, your formula returns 3 when I say there are 6 go rows! Yes, CSE entered.
I’ve triple checked, including copying the formula out of my post back into Excel, and it returns 6 for me, which I agree is the correct answer. So I’m not sure what might be happening on your end unless there is some bizarre difference in a regional setting between our computers that could be throwing something off. And I’m using Excel 2013 in case that matters.
A Boolean version:
=((W4+X4)=0)*(SUM(C4:V4)0
Did you mean
=((W4+X4)=0)*(SUM(C4:V4)=0)?
might be what he meant, might not; but neither version would work
=((W4+X4)=0)*(SUM(C4:V4)<1400) would however
No, it doesn’t work but I was asking him if that’s what he had intended. Maybe not so smart on my part.
In terms of best practice, we really should address AA5 and AA6 rather than hard coding the 20 and the 1400 cut offs. I just checked and I realise I am guilty of the same crime with my effort!
My apologies… something went wrong with my copy/paste skills :S
This is the formula I was talking about:
=((W4+X4)=0)*(SUM(C4:V4)<=1400)
=IF((AND(SUM(C4:X4) LT 1400, COUNTA(C4:X4))), “Go”, “Stop”)
=IF(AND(COUNT(C4:X4)LT=$AA$5,SUM(C4:X4)LT=$AA$6),”Go”,”Stay”)
=IF(AND(COUNT(C4:X4)<21,SUM(C4:X4)<1401),"Go","Stop")
=IF(OR(COUNT(C4:X4)>=$AA$5,SUM(C4:X4)>=$AA$6)=TRUE,”NO”,”YES”)
formula pasted in column B
this example is for row 4
=IF(W4gt0,”Stay”,IF(SUM(C4:V4)gt1400,”Stay”,”Go”))
=OR(SUM(C4:INDIRECT(“R”&TEXT(ROW(C4),”#”)&”C”&TEXT(COLUMN(C4)+$AA$5,”#”);FALSE))LT=$AA$6;ISBLANK(INDIRECT(“R”&TEXT(ROW(C4),”#”)&”C”&TEXT(COLUMN(C4)+$AA$5,”#”);FALSE)))
=IF(OR(SUM(C4:X4)>1400;COUNTA(C4:X4)>20);”no-go”; “GO”)
=IF(SUM(C4:X4)<$AA$6,IF(COUNT(C4:X4)<$AA$5,"Y","N"),"N")
=IF(AND((COUNT(C4:X4)LT=20),(SUM(C4:X4)LT=1400)),”UP”, “Down”)
=IF(AND(SUM(C4:X4)<=1400,COUNT(C4:X4)<=20),"YES","NO")
Hello,
Thank you for this interesting task.
Here is my solution:
=IF(COUNT(C4:X4)>=20;”not allowed”; IF(SUM(C4:X4)>=1400; “not allowed”; “ok”))
So far it works. Now I am going to check what are the other’s solutions. I wanna more ;-)))
Mariya
=IF(SUM(C4:X4)/($AA$6/$AA$5)>$AA$5,”Stay”,”Go”)
=IF(SUM(C4:X4)/($AA$6/$AA$5)>$AA$5,”Stay”,”Go”)
=IF(SUM(C4:X4)/($AA$6/$AA$5)>$AA$5,”Stay”,”Go”)
=IF(SUM(C4:X4)/($AA$6/$AA$5)>$AA$5,”Stay”,”Go”)
=IF(AND(IF(SUM(C4:X4)>$AA$6,”Too heavy”,”ok”)=”ok”,IF(COUNT(C4:X4)>$AA$5,”Too many”,”ok”)=”ok”),”Go”,”Stay”)
My answer is
=IF(OR(AC4>AD3;AD4>20);”Overweight”;”In weight”)
where AD3 is 1400 and AD4 is =COUNTA(C4:X4)
=IF(AND(COUNTA($C4:$X4)<=$AA$5,SUM($C4:$X4)<=$AA$6),"UP","DOWN")
=SE(E(SOMMA(C4:X4)<=AA6;CONTA.VALORI(C4:X4)<=AA5);"OK";"OVER WEIGHT")
True = Go, False = No Go
=AND(SUM(C4:X4)<=$AA$6,COUNT(C4:X4)<=$AA$5)
IF(SUM(C4:S4) LT or equal to $AA$6,IF(COUNT(C4:X4) LT or equal to AA$5,”Lift going up / down”, “Burp!!! too heavy or too many people”))
My friend always said, “a burp is never a good idea in public, unless you are in a lift, then it is better than a fart.”
Set Total_People and Total_Weight as named range (vTP & vTW). Pass that onto PQ.
Ex: TP = Excel.CurrentWorkbook(){[Name=”vTP”]}[Content]{0}[Column1]
Add 0 based index column.
Then add custom column:
=if (List.Sum(Record.ToList(#”Added Index”{[Index]}))-[Index])<=TW and (List.Count(List.RemoveNulls(Record.ToList(#"Added Index"{[Index]}))) – 1) <=TP then "yes" else "no"
A VBA formula that will return True or False and that will work for any number of cells in the specified range and also if there are blank values (or empty cell between values).
In Excel :
=cango(C4:X4,$AA$5,$AA$6)
VBA Formula =
Function CanGo(Weights As Variant, MaxPeople As Integer, MaxWeight As Integer) As Boolean
CanGo = UBound(Split(Replace(Join(Application.Transpose(Application.Transpose(Weights)), “|”), “||”, “”), “|”)) <= MaxPeople _
And WorksheetFunction.Sum(Weights) <= MaxWeight
End Function
Hmmm. Forget previous comment…
VBA formula can be much much simpler :-O
In Excel:
=CanGo(C4:X4,$AA$5,$AA$6)
In VBA:
Function CanGo(Weights As Variant, MaxPeople As Integer, MaxWeight As Integer) As Boolean
With WorksheetFunction
CanGo = .CountA(Weights) <= MaxPeople And .Sum(Weights) <= MaxWeight
End With
End Function
=IF(AND(COUNT(C4:X4)<20,SUM(C4:X4)<1400),"UP","Stay")
=IF(AND(SUM(C4:X4)<=1400,COUNT(C4:X4)<=20),"Yes","No")
Conditional format using a light green fill
=AND(C4″”,COUNTA($C4:D4<=$AA$5),SUM($C4:D4)<=$AA$6)
applied to range =$C$4:$X$13
Highlights the range that can go up/down.
CF messed up my reference, because I started in the wrong cell, clearly it should be
=AND(C4″”,COUNTA($C4:C4<=$AA$5),SUM($C4:C4)<=$AA$6)
=SE(O(CONTA.SE($C4:$X4;””&””)>$AA$5;SOMMA($C4:$X4)>$AA$6);”no”;”si”)
=IF(AND(COUNT(C4:X4)<=$AA$5,SUM(C4:X4)<=$AA$6),"UP","DOWN")
=IF((SUM(C4:X4)>1400),”OVERLOAD”,IF(COUNT(C4:X4)>20,”OVERLOAD”,”A”))
=IF(OR(COUNTA(C4:X4)>20,SUM(C4:X4)>1400),”Fail”,”Pass”)
Like many, I used IF/AND/Count & Sum, but then I added a “reason” column in column Y, referencing hidden columns AE (containing the count), AF (containing the weight). AG read the “count” column and used:
=IF(COUNT(C4:X4)>$AC$5,”Too many people”,””)
AH read the Weight column and read:
=IF(SUM(C4:X4)>$AC$6,”Too heavy”,””)
In column Y (displayed reason for no go), read:
=IF(B5=”yes”,””,(CONCAT(AG5&” “&AH5)))
=IF(SUM(C4:X4)<1400,IF(COUNT(C4:X4)<21,"GO","STAY"),"STAY")
=+SI(Y(CONTAR(C4:X4)<=20;SUMA(C4:X4)<=1400);"Go";"Stay")
Thank you for Everything Chandoo!
Happy New year!
Formula: =(SUM(C4:Y4)>1400)+(COUNT(C4:Y4)>20)
Number format: “STOP”;;”Go”
Suggest making 1400 and 20 fixed cell references rather than hard-coding in the formula.
=IF(AND(COUNT(C4:X4) <= 20,SUM(C4:X4)<=1400),"go","stop")
=IF(AND(COUNT(C4:X4)LT=$AA$5,SUM(C4:X4)LT=$AA$6),”Go”,”Stay”)
Simple:
=IF(AND(SUM($C4:$X4)<=$AA$6,COUNT($C4:$X4)<=$AA$5),"YES","NO")
=AND(SUM(C4:X4)<=$AA$6,COUNT(C4:X4)<=$AA$5)
IF(AND((SUM(C4:X4)<=1400),(COUNT(C4:X4))<=20),"GO","STOP")
Function ElevatorCanGo(MyRange As Range, MaxCount, MaxWeight) As Boolean
‘ Solves the elevator go/no go problem.
‘ KJM – 2019-01-10
‘ Input:
‘ MyRange – range of weights for each elevator passanger (any units)
‘ MaxCount – (int) maximum permissible number of people
‘ MaxWeight – (real) maximum permissible weight
‘ Returns:
‘ True if elevator can go!
Dim TotalCount As Long
Dim TotalWeight As Single
TotalCount = 0
TotalWeight = 0#
Debug.Print “”
For i = 1 To MyRange.Rows.Count
For j = 1 To MyRange.Columns.Count
If Not (IsEmpty(MyRange.Cells(i, j))) Then
Debug.Print “Cell = “; MyRange.Cells(i, j).Address; ” Weight = “; MyRange.Cells(i, j).Value
TotalCount = TotalCount + 1
TotalWeight = TotalWeight + MyRange.Cells(i, j).Value
End If
Next j
Next i
Debug.Print “Elevator range: “; MyRange.Address; ” Count = “; TotalCount; ” Total Weight = “; TotalWeight
If TotalCount > MaxCount Or TotalWeight > MaxWeight Then
ElevatorCanGo = False ‘ no go
Else
ElevatorCanGo = True ‘ go!
End If
End Function
Hi,
I thick following formula is the simplest one to go for this problem.
=IF((IF(COUNT(C4:X4)>=20,0,1)*IF(SUM(C4:X4)>=1400,0,1))=1,”YES”,”NO”)
instead of putting “20” and “1400” we can link cell reference to robust the formula.
Let me know your opinion with this
=IF(AND(SUM(C4:X4)<=1400,COUNT(C4:X4)<=20),"Can go","Can't go")
=IF(AND(COUNTA(C4:X4)<=20,SUM(C4:X4)<=1400)," MOVE "," STAY ")
=SUM(C4:X4)/($AA$6/$AA$5)
Create a custom number format
[>20]”Stay”;;”Go”;@
work for me
=IF(COUNT(C4:X4)<=20,IF(SUM(C4:X4)<=1400,"GO","STAY"),"STAY")
=IF(OR(COUNTA(C4:X4)LT=AA5;SUM(C4:X4)LT=AA6);”YES”;”NO”)
This one almost nail it but it fails.
=IF(OR(COUNTA(C5:X5)LT=AA5;SUM(C5:X5)LT=AA6);”YES”;”NO”)
This will do the work:
=IF(AND(COUNTA(C5:X5)LT=AA5;SUM(C5:X5)LT=AA6);”YES”;”NO”)
This one is correctly applied you guys can try
=IF(SUM(C4:X4)>1400,”STAY”,IF(COUNTA(C4:X4)>20,”STAY”,”GO”))
=IF(AND(COUNT(C4:X4)<$AA$5,SUM(C4:X4)<$AA$6),"GO","NO")
=IF(OR(COUNT(C4:X4)<20,AND(SUM(C4:X4)<1400)=TRUE),"Can Go","Can Not Go")
=+IF(COUNT(C4:X4)>20,”No”,IF(SUM(C4:X4)>1400,”No”,”Yes”))
=IF(AND(COUNT(C4:X4)>20,SUM(C4:X4)>1400),”Too Heavy & Too Many”,”Move Lift”)
I used if & and formula jointly to reach solution
=IF(SUM(C4:X4)<=1400,IF(COUNT(C4:X4)<=20,"YES","NO"),"NO")
I solved this problem by giving this formula .
I use “AND” because in IF condition we need to satisfy two condition same time and “AND” is used to satisfy both condition.
=IF((AND(COUNT(C4:X4)>20, SUM(C4:X4)>1400)),”CAN NOT GO”, “CAN GO”)
Hi Test
=IF((SUM(C4:X4)>1400)+(COUNT(C4:X4)>20),”STAY”,”GO”)
=IF(COUNT(C4:X4)<=20,IF(SUM(C4:X4)<=1400,"GO","NO GO"),"NO GO")
=IF(AND(COUNT(C4:X4)<=$AA$5,SUM(C4:X4)<=$AA$6),"go","stay")
=IF(AVERAGEIFS(C4:X4,C4:X4,””””)*(IF(COUNTA(C4:X4)>$AA$5,1000,COUNTA(C4:X4)))>$AA$6,”No”,”Yes”)
=IF(AVERAGEIFS(C4:X4,C4:X4,””””)*(IF(COUNTA(C4:X4)>$AA$5,1000,COUNTA(C4:X4)))>$AA$6,”No”,”Yes”)