0

Please help, I can't figure out the equation to solve a real-world problem.

Here is the case: Each employee can pick up the customer if he/she comes to store first (first come first serve). If he/she make >=12 dollars more than other employees, then rotate to next employee that comes after. If he/she makes < 12 dollars, then continue picking up the next customer.

Excel image

Come to store:   -----1st  -----    2nd  ----  3rd   ------     4th    -----    5th   ----     6th   ---   7th 

Employee name: Apple  --  Bear  --  Conny  --    Donny  --    Elvis   --   Fell     -- Gith

Total $: ---------------33   -----    28   -------     20   -------      23  -------      22   ----     15  ----      2

Who turns next?   
It should be: -------- 2nd   ---    3rd  ------    4th    ------     5th  ------    6th ----      7th    -   1st                                               

As you can see, It is Gith turn to pick up the next customer, Gith only made 2 dollars, the difference is >=12 dollars between Gith and other employees. It is Apple turn after Gith even though Apple made more than Fell >=12 dollars, but because other employees in between like Conny Donny Elvis who doesn't make >=12 dollars more than Fell, so it is Apple turn.

How can I come up with an equation that could solve this situation?

  • 2
    According to "If he/she makes < 12 dollars, then continue picking up the next customer." .. Gith only made 2 dollars.. so the next customer is supposed to be attended by Gith.. not Apple. – p._phidot_ Jan 11 at 8:35
  • That is true, as I mentioned that Apple takes the second turn after Gith – Nghi Hoang Jan 11 at 18:42
  • If in the next sale, Gith only made 10 dollars.. will it be sufficient to pass to Apple? – p._phidot_ Jan 16 at 3:01
1

Here is one answer. Fill in C4,D4,E4,F4,G4,H4,I4 with the following to know if you should skip them: (C3 at the start should be the cell right above, ie D4,E4 etc..)

=IF(C3>(LARGE($C3:$I3,2)+12),"skip","")

Then fill in C5 with the following to know if someone is jumping out of order:

=IF(SMALL(C3:I3,1)<(SMALL(C3:I3,2)-12),INDIRECT(ADDRESS(2,2+(MATCH(SMALL(C3:I3,1),C3:I3,0)))),"Next non-skip")

What we don't know: Who served someone last?

Edit: return name of person jumping out of order rather than dollar amount.

  • Thank you for helping, but if I change the amount in Fell to 3 dollars. Should it be Fell first then Gith second? I only get "Next non-skip" as a result instead of Fell. My goal is to display all the employee names from first to last – Nghi Hoang Jan 11 at 18:33
  • My formula is working on if they made less than 12 dollars than anyone else. When changing Fell to 3, that no longer holds true. I would say that Gith then Fell. But for a logic formula we would need to determine if the condition is "making 12 dollars less than anyone else" or making 12 dollars less than everyone else" or some number of people in the middle. Fell @ 15 is already 12 less than Apple and Bear. – Brian Jan 11 at 19:42
  • =IF(SMALL($C3:$I3,1)<(SMALL($C3:$I3,2)-11),INDIRECT(ADDRESS(2,2+(MATCH(SMALL($C3:$I3,1),$C3:$I3,0)))),"Next non-skip") – Nghi Hoang Jan 11 at 20:09
  • looks like my answer above had a double equals "==" at the start. With one equal sign it looks to work on my side. – Brian Jan 11 at 20:14
  • =IF(SMALL($C3:$I3,1)<(SMALL($C3:$I3,2)-11),INDIRECT(ADDRESS(2,2+(MATCH(SMALL($C3:$I3,1),$C3:$I3,0)))),"Next non-skip"), You are right, I change 12 to 11 to fit my need, and plus =IF(SMALL($C3:$I3,2)<(SMALL($C3:$I3,3)-11),INDIRECT(ADDRESS(2,2+(MATCH(SMALL($C3:$I3,2),$C3:$I3,0)))),"Next non-skip") to the next cell inorder to display Fell. If all the name can't be displayed, is there a way to display the number as I have mentioned above? Ex. 1, 2 3, 4 relative to who picks up the customer first, second, third? I'm able to link the number to the name. If that's easier to show – Nghi Hoang Jan 11 at 20:23
0

Following your shared table..

Fill in there labels :

B6  ---->  currentNo :
B7  ---->  nextNo :
B9  ---->  Next Name :

Then these formulas :

C6  ---->  =MATCH(C2,{"Apple","Bear","Conny","Donny","Elvis","Fell","Gith"},0)
C7  ---->  =IF(C3>=12,IF((C6+1)=8,1,C6+1),C6)
C9  ---->  =INDEX({"Apple","Bear","Conny","Donny","Elvis","Fell","Gith"},C7)
D2  ---->  =C9

Then drag all right-wards.

Done. Hope it helps. ( :

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.