fbpx
Search
Close this search box.

Elevator problem – Excel homework

Excel Challenges - 192 comments

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.

Elevator problem - excel formula homework

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.

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

192 Responses to “Elevator problem – Excel homework”

  1. Shobi Imran says:

    Very good post. Keep it up.
    Thanks for sharing sample data.

    • Josh says:

      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.

      • Diogo Cuba says:

        +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.

  2. Art says:

    What's the trick?!
    =IF(AND(COUNT($C4:$X4) LT= $AA$5;SUM($C4:$X4) LT= $AA$6);"Yes";"No")

  3. Sumanth says:

    =IF(AND(COUNT(C4:X4)LT 21,SUM(C4:X4) LT 1400),"Go","Stay")

  4. Rajesh Kumar Singh says:

    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

  5. Niefer says:

    =IF(AND(COUNT(C4:X4)<=AA$5,SUM(C4:X4)<=AA$6),"Go","Stay")

  6. Manoj says:

    =IF(AND(COUNT(C4:X4)<=20,SUM(C4:X4)<=1400),"Go","Stay")

  7. UWEISS says:

    =IF(COUNT(C4:Y4)<=AA$5*(SUM(C4:Y4)<=AA$6),"YES","")

  8. JoAnn Paules says:

    =IF(AND((COUNT(C4:X4)<=$AA$5),(SUM(C4:X4)<=$AA$6)),"Yes","No")

  9. Roger Govier says:

    =IF((COUNT(C4:X4)<=$AA$5)*(SUM(C4:X4)<=$AA$6),"Go","Stop")

  10. Hazel McLaren says:

    =IF(OR(COUNT(C4:X4)RT$AA$5,(SUM(C4:X4)RT$AA$6)),"STOP","GO")

  11. Eric says:

    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

  12. Dan says:

    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")

  13. Wruf says:

    =IF(NOT($W4)*(SUM(C4:X4)<=$AA$6),"Go","Stop")

  14. Pramod says:

    =IF(AND(SUM(C4:X4)<=1400,COUNT(C4:X4)<=20),"Yes","No")

  15. Lorenzo says:

    =IF(OR(SUM(C5:X5)GT1400,COUNT(C5:X5)GT20),"NO","YES")

  16. Cesar says:

    =IF(OR(SUM(C4:X4)>$AA$6;COUNT(C4:X4)>$AA$5);"Stop";"GO")

  17. Kathy Dawson says:

    =IF(AND((COUNT(C4:X4)<=$AA$5),(SUM(C4:X4)<=$AA$6)),"Go","Stay")

  18. Scott says:

    =IF(OR(COUNT(C4:X4)>$AA$5,SUM(C4:X4)>$AA$6),"Stay","Go")

  19. Cesar says:

    "=IF(OR(SUM(C4:X4)>$AA$6;COUNT(C4:X4)>$AA$5);"Stop";"GO")"

  20. Lorenzo says:

    =IF(OR(SUM(C4:X4)GT1400,COUNT(C4:X4)GT20),"NO","YES")

  21. Kathy Dawson says:

    =IF(AND((COUNT(C4:X4)<=$AA$5),(SUM(C4:X4)<=$AA$6)),"Go","Stay")

    Copy down to remaining rows.

  22. Vijayan says:

    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

  23. jim says:

    {=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!

  24. =IF(AND(COUNTIFS(C4:X4,">0")<=20,SUM(C4:X4)<1400)*1=1,"GO","NO GO")

  25. Don Hopkins says:

    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.

  26. Abbott Katz says:

    I may be missing something, but in B4 I entered:
    =IF(AND(COUNT(C4:X4)LT21,SUM(C4:X4)LT1401),"Yes","No")

  27. Cesar says:

    {=IF(OR(SUM(C4:X4)>$AA$6;COUNT(C4:X4)>$AA$5);"Stop";"Go")}

  28. Mujibur says:

    =IF(SUM(C4:X4)<=1400,"Lift can move","")

  29. Pablo says:

    I entered this formula:
    =IF(AND(SUM($C4:$X4)LT=1400;COUNT($C4:$X4)LT=20);"Can";"Can't")

  30. Rajender says:

    I will use this formula =IF(AND(SUM(C4:X4)<=$AA$6,COUNT(C4:X4)<=20),"Go","Not Go")

  31. John says:

    =IF(AND(COUNT(C4:X4)<=20,SUM(C4:X4)<=1400),"Go","Stay")

  32. Jack says:

    Short and sweet:
    =IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),"No","Yes")

  33. John says:

    =IF(AND(COUNT(C4:X4)<=AA$5,SUM(C4:X4)<=AA$6),"Go","Stay")

  34. Bill Wood says:

    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")

  35. Nathan says:

    This seems to work:
    =IF(AND(COUNTA(C4:X4)<$AA$5+1,SUM(C4:X4)<$AA$6+1),"Good to go","PROBLEM")

  36. Pam says:

    =IF(AND(COUNTA(C4:X4)<=$AC$5,SUM(C4:X4)<=$AC$6),"GO","STAY")

  37. Jon says:

    =IF((--(COUNT(C4:X4)<=$AA$5)+--(SUM(C4:X4)=2, "YES", "NO")

  38. Andrew says:

    =IF(AND(COUNT(C4:X4)<21,SUM(C4:X4)<1401),"YES","NO")

  39. Andrew says:

    ...or this...
    =IF(AND(COUNT(C4:X4)<$AA$5,SUM(C4:X4)<$AA$6),"YES","NO")

  40. Alex Ma says:

    =IF(AND((COUNTA(C4:X4)<=20),(SUM(C4:X4)<=1400)),"Y","N")

  41. Frank McCraw says:

    =IF(OR(COUNTA(C4:X4)>$AA$5,SUM(C4:X4)>$AA$6),"No","OK")

  42. DanO says:

    =IF(AND((COUNTA(C3:X3)<=20), (SUM(C4:X4)<=1400)),"GO","NO GO")"

  43. DanO says:

    =IF(AND((COUNTA(C4:X4)<=20), (SUM(C4:X4)<=1400)),"GO","NO GO")

  44. Alfonso López says:

    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")

  45. Ravi says:

    =IF(AND(COUNT(C4:X4)<=20,SUM(C4:X4)<=1400),"go","stay")

    please correct if I am wrong

  46. Raphael Valente says:

    in portuguese:

    =SE(E(SOMA(E4:Z4)<=$AC$6;CONT.VALORES(E4:Z4)<=$AC$5);"Y";"N")

  47. Richard D says:

    =IF(AND(COUNTA(C4:X4)LT=AA$5,SUM(C4:X4)LT=AA$6),"GO","STAY")

  48. Jan Martens says:

    IF((SUM(OFFSET(C4,,,1,COUNTA(C4:Z4)))<1401)*COUNTA(C4:Z4)<21,"go","no go")

  49. Eric says:

    (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

  50. lee hibbert says:

    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.

  51. NeilF says:

    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)

  52. Jeff says:

    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")

  53. 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?!

    • Duncan Williamson says:

      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.

  54. Michael Kimber says:

    my solution:

    =IF(AND((COUNT(C4:X4)<21),(SUM(C4:X4)<1401)),"GO","STAY")

  55. Oscar Rodríguez says:

    =IF(AND(COUNTIF(C4:X4,">0")<=$AA$5,SUMIF(C4:X4,"<="&$AA$6)),"GO","STAY")

  56. Mehta says:

    =IF(AND(COUNT(C4:X4)<=$AA$5,SUM(C4:X4)<=$AA$6),"YES","NO")

  57. Lily Tran says:

    =IF(AND(SUM(C4:X4)<=$AA$6,COUNTA(C4:X4)<=$AA$5),"CAN GO","CAN'T GO")

  58. Sagar says:

    =IF(AND(COUNT(C4:X4)<=$AA$5,SUM(C4:X4)<=$AA$6),"Yes","No")

  59. Walter W. says:

    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"

  60. Sravan K says:

    =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.

  61. Hiren says:

    =IF(AND(COUNT(C4:X4)<=20,SUM(C4:X4)<=1400),"Yes","No")

  62. Sushil says:

    If((And(Count(C4:X4) <=$AA$5,SUM(C4:X4)<=$AA$6)),"Yes","No")

  63. I'll try haiku..

    I look around me.
    "Lady, how much do you weigh?"
    Slap to the face. Ding!

  64. Orlando Rainey says:

    My solution is given below

    =IF(AND(($AA$5-COUNTA($C4:$X4) >=0),($AA$6-SUM($C4:$X4) >=0)),"YES","NO")

  65. 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"

  66. DOLLAR CHANDE says:

    =IF(+COUNT(C4:X4)>20,"Stay",IF(SUM(C4:X4)<=1400,"Go","Stay"))

  67. Pravin says:

    +IF(COUNT(C8:X8)LT21,IF(SUM(C8:X8)LT1400,"Go","Dontgo"),"Dontgo")

  68. Yash Lakhotia says:

    =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.

  69. Lalit Kumar (Lucky) says:

    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")

  70. Yash says:

    =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.

  71. Lalit Kumar (Lucky) says:

    Please See the Solution.

    =IF(OR(COUNT(C4:X4)GT20,SUM(C4:X4)GT1400),"Over Weight","No Problem")

  72. Barry says:

    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.

  73. Vivek V Phadke says:

    =IF(AND(COUNT(C4:X4)<=$AA$5,SUM(C4:X4)<=$AA$6),"Go","Stop")

  74. Shofiyudin Musthofa says:

    I use this formula to solve the Problem
    =IF(OR(COUNT(C4:X4)>=20,SUM(C4:X4)>1400),"CAN'T GO","CAN GO")

  75. Lalit Kumar says:

    Please See My Answer Below:-

    =IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),"Over Weight","No Problem")

  76. Lalit says:

    =IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),"Over Weight","No Problem")

  77. Vivek V Phadke says:

    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

  78. Lalit Kumar (Lucky) says:

    My Answer Is :-

    =IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),"Over Weight","No Problem")

  79. Lalit Kumar says:

    =IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),"Over Weight","No Problem")

  80. Lalit Kumar says:

    =IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),"Over Weight","No Problem")

  81. Mukes Bajaj says:

    =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")))

  82. satish says:

    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)

  83. yogi says:

    '=+IF(AND(IF(COUNT(F4:AA4)<=AD5,1,0),IF(SUM(F4:AA4)<=AD6,1,0)),1,0)

  84. Mehar Ali Khatri says:

    =IF(OR(COUNT(C4:X4)>$AA$5,SUM(C4:X4)>$AA$6),"Stop","Go")

  85. Samir Kothari says:

    =IF(COUNT(C4:X4)>$AA$5,"Stay",IF(SUM(C4:X4)>$AA$6,"Stay","Go"))

  86. =IF(AND(SUM(C4:X4)> 1400,COUNT(C4:X4)<21),"UP","Down")

    Looking for a more efficient solution.

  87. Manoj Aggarwal says:

    =IF(AND(COUNT(C4:X4)<=20,SUM(C4:X4)<=1400),"Go","Stop")

  88. Martin says:

    =IF(OR(COUNT(C4:X4) GT 20,SUM(C4:X4) GT 1400),"Don't go","GO")

  89. Gary Skelton says:

    Hi,

    =IF(IF(SUM(C4:X4)>$AA$6,1,0)+IF(COUNTA(C4:X4)>$AA$5,1,0)=2,"Stay","Go")

    Gaz

  90. xaratsarhs says:

    =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

  91. pavan says:

    =IF(OR(COUNT(C4:X4)>20,SUM( C4:X4)>1400),"can't go","Can go")

  92. =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.

  93. Mihail Iadkov says:

    =IF(AND(COUNTA(C4:X4)LT$AA$5;SUM(C4:X4)LT$AA$6);"Yes";"No")

  94. Sujan Gurung says:

    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

  95. Ronak says:

    =+IF(OR(COUNT(C4:X4)>$AA$5,SUM(C4:X4)>$AA$6),"Stop","Go")

  96. Deepa R says:

    =IF(OR(SUM(C4:X4)>$AA$6,COUNTA(C4:X4)>$AA$5),"Stop","Go")

  97. Ignacio De Bustamante says:

    =SI(Y(SUMA(C4:X4)<=1400;CONTAR(C4;X4)<=20);"YES";"NO")

  98. Alain Van Holder says:

    =IF(AND(COUNT($C4:$X4)<=$AA$5;SUM($C4:$X4)<=$AA$6);"Go";"Stay")

  99. Lalit Kumar says:

    Please see my answer

    =IF(OR(COUNT(C4:X4)>20,SUM(C4:X4)>1400),"Over Weight","No Problem")

  100. Vijay says:

    =IF(OR(COUNT(C4:X4)>AA$5,SUM(C4:X4)>AA$6),"Can't Go", "Can Go")

  101. jim says:

    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

    • Duncan Williamson says:

      This breaks in rows 8 and 13. Don't forget the table extends to column X

      • jim says:

        How so?
        if there are entries in column W and X then it has already failed due to too many occupants

        • Duncan Williamson says:

          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

          • jim says:

            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

    • Harold says:

      While this works OK
      You could silence your critics;
      Wrap it all with NOT()
      ~~~
      =NOT(W4+(SUM(C4:V4)%>14))

  102. '=IF(AND(COUNT($C4:$X4)<=$AA$5,SUM($C4:$X4)<=$AA$6),"GO","STOP")

  103. Marcelo Alencar says:

    =IF(COUNT(C4:X4)>=$AA$5,"Stop",IF(SUM(C4:X4)>=$AA$6,"STOP","GO"))

  104. David N says:

    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?

    • Chandoo says:

      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?

      • David N says:

        Array entered...
        {=SUMPRODUCT((MMULT(SIGN(C4:X13),TRANSPOSE(SIGN(COLUMN(C3:X3))))<=AA5)*(MMULT(1*C4:X13,TRANSPOSE(SIGN(COLUMN(C3:X3))))<=AA6))}

        • Duncan Williamson says:

          David, your formula returns 3 when I say there are 6 go rows! Yes, CSE entered.

          • David N says:

            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.

  105. Xiq says:

    A Boolean version:
    =((W4+X4)=0)*(SUM(C4:V4)0

    • Duncan Williamson says:

      Did you mean

      =((W4+X4)=0)*(SUM(C4:V4)=0)?

      • jim says:

        might be what he meant, might not; but neither version would work
        =((W4+X4)=0)*(SUM(C4:V4)<1400) would however

        • Duncan Williamson says:

          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!

      • Xiq says:

        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)

  106. Garu says:

    =IF((AND(SUM(C4:X4) LT 1400, COUNTA(C4:X4))), "Go", "Stop")

  107. Chris Hartnell says:

    =IF(AND(COUNT(C4:X4)LT=$AA$5,SUM(C4:X4)LT=$AA$6),"Go","Stay")

  108. Greg says:

    =IF(AND(COUNT(C4:X4)<21,SUM(C4:X4)<1401),"Go","Stop")

  109. Nicholas Voisin says:

    =IF(OR(COUNT(C4:X4)>=$AA$5,SUM(C4:X4)>=$AA$6)=TRUE,"NO","YES")

  110. Vane Hugo says:

    formula pasted in column B
    this example is for row 4
    =IF(W4gt0,"Stay",IF(SUM(C4:V4)gt1400,"Stay","Go"))

  111. Maciej says:


    =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)))

  112. Fred says:

    =IF(OR(SUM(C4:X4)>1400;COUNTA(C4:X4)>20);"no-go"; "GO")

  113. ALTUG ALTINTAS says:

    =IF(SUM(C4:X4)<$AA$6,IF(COUNT(C4:X4)<$AA$5,"Y","N"),"N")

  114. Khaled Abdel Aziz says:

    =IF(AND((COUNT(C4:X4)LT=20),(SUM(C4:X4)LT=1400)),"UP", "Down")

  115. Avi says:

    =IF(AND(SUM(C4:X4)<=1400,COUNT(C4:X4)<=20),"YES","NO")

  116. Mariya says:

    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

  117. Gian All says:

    =IF(SUM(C4:X4)/($AA$6/$AA$5)>$AA$5,"Stay","Go")

  118. Giancarlo says:

    =IF(SUM(C4:X4)/($AA$6/$AA$5)>$AA$5,"Stay","Go")

  119. Giancarlo says:

    =IF(SUM(C4:X4)/($AA$6/$AA$5)>$AA$5,"Stay","Go")

  120. Eva says:

    =IF(AND(IF(SUM(C4:X4)>$AA$6,"Too heavy","ok")="ok",IF(COUNT(C4:X4)>$AA$5,"Too many","ok")="ok"),"Go","Stay")

  121. ALBERTO OLEOTTI says:

    My answer is
    =IF(OR(AC4>AD3;AD4>20);"Overweight";"In weight")
    where AD3 is 1400 and AD4 is =COUNTA(C4:X4)

  122. Suraj Nair says:

    =IF(AND(COUNTA($C4:$X4)<=$AA$5,SUM($C4:$X4)<=$AA$6),"UP","DOWN")

  123. ALBERTO says:

    =SE(E(SOMMA(C4:X4)<=AA6;CONTA.VALORI(C4:X4)<=AA5);"OK";"OVER WEIGHT")

  124. Chirayu says:

    True = Go, False = No Go

    =AND(SUM(C4:X4)<=$AA$6,COUNT(C4:X4)<=$AA$5)

  125. Kayak6000 says:

    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"))

  126. Chihiro says:

    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"

  127. GC Excel says:

    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

  128. GC Excel says:

    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

  129. Arvinder Sahni says:

    =IF(AND(COUNT(C4:X4)<20,SUM(C4:X4)<1400),"UP","Stay")

  130. Francis says:

    =IF(AND(SUM(C4:X4)<=1400,COUNT(C4:X4)<=20),"Yes","No")

  131. GraH says:

    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.

    • GraH says:

      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)

  132. gianfranco pevere says:

    =SE(O(CONTA.SE($C4:$X4;""&"")>$AA$5;SOMMA($C4:$X4)>$AA$6);"no";"si")

  133. Bhavani says:

    =IF(AND(COUNT(C4:X4)<=$AA$5,SUM(C4:X4)<=$AA$6),"UP","DOWN")

  134. Divvakar says:

    =IF((SUM(C4:X4)>1400),"OVERLOAD",IF(COUNT(C4:X4)>20,"OVERLOAD","A"))

  135. Utkarsh says:

    =IF(OR(COUNTA(C4:X4)>20,SUM(C4:X4)>1400),"Fail","Pass")

  136. Val says:

    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)))

  137. MONIKA AGGARWAL says:

    =IF(SUM(C4:X4)<1400,IF(COUNT(C4:X4)<21,"GO","STAY"),"STAY")

  138. Gerardo Gomez says:

    =+SI(Y(CONTAR(C4:X4)<=20;SUMA(C4:X4)<=1400);"Go";"Stay")
    Thank you for Everything Chandoo!
    Happy New year!

  139. Dan says:

    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.

  140. Tharun says:

    =IF(AND(COUNT(C4:X4) <= 20,SUM(C4:X4)<=1400),"go","stop")

  141. Abid Hussain says:

    =IF(AND(COUNT(C4:X4)LT=$AA$5,SUM(C4:X4)LT=$AA$6),"Go","Stay")

  142. Steve says:

    Simple:

    =IF(AND(SUM($C4:$X4)<=$AA$6,COUNT($C4:$X4)<=$AA$5),"YES","NO")

  143. MikeO3 says:

    =AND(SUM(C4:X4)<=$AA$6,COUNT(C4:X4)<=$AA$5)

  144. Ajay Anand says:

    IF(AND((SUM(C4:X4)<=1400),(COUNT(C4:X4))<=20),"GO","STOP")

  145. Kirby says:

    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

  146. Bhimarao Naik says:

    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

  147. Moteb says:

    =IF(AND(SUM(C4:X4)<=1400,COUNT(C4:X4)<=20),"Can go","Can't go")

  148. Sheshaank Pydikondala says:

    =IF(AND(COUNTA(C4:X4)<=20,SUM(C4:X4)<=1400)," MOVE "," STAY ")

  149. Gian All says:

    =SUM(C4:X4)/($AA$6/$AA$5)

    Create a custom number format
    [>20]"Stay";;"Go";@

  150. ari says:

    work for me
    =IF(COUNT(C4:X4)<=20,IF(SUM(C4:X4)<=1400,"GO","STAY"),"STAY")

  151. Diogo Cuba says:

    =IF(OR(COUNTA(C4:X4)LT=AA5;SUM(C4:X4)LT=AA6);"YES";"NO")

  152. Diogo Cuba says:

    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")

  153. Syed Mohammad Haris says:

    This one is correctly applied you guys can try
    =IF(SUM(C4:X4)>1400,"STAY",IF(COUNTA(C4:X4)>20,"STAY","GO"))

  154. Josh says:

    =IF(AND(COUNT(C4:X4)<$AA$5,SUM(C4:X4)<$AA$6),"GO","NO")

  155. PRAMOD DUBEY says:

    =IF(OR(COUNT(C4:X4)<20,AND(SUM(C4:X4)<1400)=TRUE),"Can Go","Can Not Go")

  156. Gregor says:

    =+IF(COUNT(C4:X4)>20,"No",IF(SUM(C4:X4)>1400,"No","Yes"))

  157. Sanjeew says:

    =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

  158. Malhar Anarse says:

    =IF(SUM(C4:X4)<=1400,IF(COUNT(C4:X4)<=20,"YES","NO"),"NO")

  159. Arshad says:

    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")

  160. Gian All says:

    Hi Test
    =IF((SUM(C4:X4)>1400)+(COUNT(C4:X4)>20),"STAY","GO")

  161. Mike says:

    =IF(COUNT(C4:X4)<=20,IF(SUM(C4:X4)<=1400,"GO","NO GO"),"NO GO")

  162. Sushmita Bhar says:

    =IF(AND(COUNT(C4:X4)<=$AA$5,SUM(C4:X4)<=$AA$6),"go","stay")

  163. Esakki Rajesh M says:

    =IF(AVERAGEIFS(C4:X4,C4:X4,"""")*(IF(COUNTA(C4:X4)>$AA$5,1000,COUNTA(C4:X4)))>$AA$6,"No","Yes")

    • Esakki Rajesh M says:

      =IF(AVERAGEIFS(C4:X4,C4:X4,"""")*(IF(COUNTA(C4:X4)>$AA$5,1000,COUNTA(C4:X4)))>$AA$6,"No","Yes")

Leave a Reply


« »