0

consider the following sheet

ID Hours
1  16
2  24
3  50
4  36

now I need to match the values in Col B to one of 3 variables

16, 25, 30

so what I'm trying to do is match the number in B2:B to the closest number of the above three variables... not able to work this out with =match... can anyone help?

adding a sheet for clarity:

Basically, what I want to do is assign a variable to a store based on their hours. so if ID 3 is 50 then they will be assigned the variable of 30 and have 20 left over, so essentially they have will two variables, one of 30 one of 16 with a remainder of 4. I've done this now with a few if/match statements but I was wondering if there was a one formula method to match one variable to another.

sheet: https://docs.google.com/spreadsheets/d/

  • 1
    what could be the maximum base hour? 100? more? – user0 Sep 10 '18 at 20:14
  • 1
    also, you want all results in one column? – user0 Sep 10 '18 at 20:18
  • it could be anything from 12 to 150 its based on a sales forecast. More than one column is fine if we are observing one observation per cell. – Datanovice Sep 10 '18 at 20:20
1

in case you want to round it between 3 numbers on a linear plane use nested IF like:

=IF(    B2 <  20.5 ; 16;
 IF(AND(B2 >= 20.5 ;
        B2 <  27.5); 25;
 IF(    B2 >= 27.5 ; 30; )))

which divides plain into:

             16        25    30
-∞ __________|_________|_____|___________ ∞ +
                  |       |
                 20.5    27.5

enter image description here


UPDATE:

basically, all you need is this simple formula pasted in C2 and dragged down -
(it can devide inputs from B column up to 360h):

=SPLIT(IF(AND(B2>=16;B2<25);16;IF(AND(B2>=25;B2<30);25;IF(B2>=30;30;)))&" |"& IF(SUM(B2-30)>=16;IF(SUM(B2-30)<25;16;IF(AND(SUM(B2-30)>=25;SUM(B2-30)<30);25;IF(SUM(B2-30)>=30;30;)))&"|";IF(SUM(B2-30)>=0;IF(SUM(B2-30)=0;;SUM(B2-30));IF(SUM(B2-25)>=0;IF(SUM(B2-25)=0;;SUM(B2-25));IF(SUM(B2-16)>0;SUM(B2-16);))))& IF(SUM(B2-SUM(30*2))>=16;IF(SUM(B2-SUM(30*2))<25;16;IF(AND(SUM(B2-SUM(30*2))>=25;SUM(B2-SUM(30*2))<30);25;IF(SUM(B2-SUM(30*2))>=30;30;)))&"|";IF(SUM(B2-30-30)>=0;IF(SUM(B2-30-30)=0;;SUM(B2-30-30));IF(SUM(B2-30-25)>=0;IF(SUM(B2-30-25)=0;;SUM(B2-30-25));IF(SUM(B2-30-16)>0;SUM(B2-30-16);))))& IF(SUM(B2-SUM(30*3))>=16;IF(SUM(B2-SUM(30*3))<25;16;IF(AND(SUM(B2-SUM(30*3))>=25;SUM(B2-SUM(30*3))<30);25;IF(SUM(B2-SUM(30*3))>=30;30;)))&"|";IF(SUM(B2-SUM(30*2)-30)>=0;IF(SUM(B2-SUM(30*2)-30)=0;;SUM(B2-SUM(30*2)-30));IF(SUM(B2-SUM(30*2)-25)>=0;IF(SUM(B2-SUM(30*2)-25)=0;;SUM(B2-SUM(30*2)-25));IF(SUM(B2-SUM(30*2)-16)>0;SUM(B2-SUM(30*2)-16);))))& IF(SUM(B2-SUM(30*4))>=16;IF(SUM(B2-SUM(30*4))<25;16;IF(AND(SUM(B2-SUM(30*4))>=25;SUM(B2-SUM(30*4))<30);25;IF(SUM(B2-SUM(30*4))>=30;30;)))&"|";IF(SUM(B2-SUM(30*3)-30)>=0;IF(SUM(B2-SUM(30*3)-30)=0;;SUM(B2-SUM(30*3)-30));IF(SUM(B2-SUM(30*3)-25)>=0;IF(SUM(B2-SUM(30*3)-25)=0;;SUM(B2-SUM(30*3)-25));IF(SUM(B2-SUM(30*3)-16)>0;SUM(B2-SUM(30*3)-16);))))& IF(SUM(B2-SUM(30*5))>=16;IF(SUM(B2-SUM(30*5))<25;16;IF(AND(SUM(B2-SUM(30*5))>=25;SUM(B2-SUM(30*5))<30);25;IF(SUM(B2-SUM(30*5))>=30;30;)))&"|";IF(SUM(B2-SUM(30*4)-30)>=0;IF(SUM(B2-SUM(30*4)-30)=0;;SUM(B2-SUM(30*4)-30));IF(SUM(B2-SUM(30*4)-25)>=0;IF(SUM(B2-SUM(30*4)-25)=0;;SUM(B2-SUM(30*4)-25));IF(SUM(B2-SUM(30*4)-16)>0;SUM(B2-SUM(30*4)-16);))))& IF(SUM(B2-SUM(30*6))>=16;IF(SUM(B2-SUM(30*6))<25;16;IF(AND(SUM(B2-SUM(30*6))>=25;SUM(B2-SUM(30*6))<30);25;IF(SUM(B2-SUM(30*6))>=30;30;)))&"|";IF(SUM(B2-SUM(30*5)-30)>=0;IF(SUM(B2-SUM(30*5)-30)=0;;SUM(B2-SUM(30*5)-30));IF(SUM(B2-SUM(30*5)-25)>=0;IF(SUM(B2-SUM(30*5)-25)=0;;SUM(B2-SUM(30*5)-25));IF(SUM(B2-SUM(30*5)-16)>0;SUM(B2-SUM(30*5)-16);))))& IF(SUM(B2-SUM(30*7))>=16;IF(SUM(B2-SUM(30*7))<25;16;IF(AND(SUM(B2-SUM(30*7))>=25;SUM(B2-SUM(30*7))<30);25;IF(SUM(B2-SUM(30*7))>=30;30;)))&"|";IF(SUM(B2-SUM(30*6)-30)>=0;IF(SUM(B2-SUM(30*6)-30)=0;;SUM(B2-SUM(30*6)-30));IF(SUM(B2-SUM(30*6)-25)>=0;IF(SUM(B2-SUM(30*6)-25)=0;;SUM(B2-SUM(30*6)-25));IF(SUM(B2-SUM(30*6)-16)>0;SUM(B2-SUM(30*6)-16);))))& IF(SUM(B2-SUM(30*8))>=16;IF(SUM(B2-SUM(30*8))<25;16;IF(AND(SUM(B2-SUM(30*8))>=25;SUM(B2-SUM(30*8))<30);25;IF(SUM(B2-SUM(30*8))>=30;30;)))&"|";IF(SUM(B2-SUM(30*7)-30)>=0;IF(SUM(B2-SUM(30*7)-30)=0;;SUM(B2-SUM(30*7)-30));IF(SUM(B2-SUM(30*7)-25)>=0;IF(SUM(B2-SUM(30*7)-25)=0;;SUM(B2-SUM(30*7)-25));IF(SUM(B2-SUM(30*7)-16)>0;SUM(B2-SUM(30*7)-16);))))& IF(SUM(B2-SUM(30*9))>=16;IF(SUM(B2-SUM(30*9))<25;16;IF(AND(SUM(B2-SUM(30*9))>=25;SUM(B2-SUM(30*9))<30);25;IF(SUM(B2-SUM(30*9))>=30;30;)))&"|";IF(SUM(B2-SUM(30*8)-30)>=0;IF(SUM(B2-SUM(30*8)-30)=0;;SUM(B2-SUM(30*8)-30));IF(SUM(B2-SUM(30*8)-25)>=0;IF(SUM(B2-SUM(30*8)-25)=0;;SUM(B2-SUM(30*8)-25));IF(SUM(B2-SUM(30*8)-16)>0;SUM(B2-SUM(30*8)-16);))))& IF(SUM(B2-SUM(30*10))>=16;IF(SUM(B2-SUM(30*10))<25;16;IF(AND(SUM(B2-SUM(30*10))>=25;SUM(B2-SUM(30*10))<30);25;IF(SUM(B2-SUM(30*10))>=30;30;)))&"|";IF(SUM(B2-SUM(30*9)-30)>=0;IF(SUM(B2-SUM(30*9)-30)=0;;SUM(B2-SUM(30*9)-30));IF(SUM(B2-SUM(30*9)-25)>=0;IF(SUM(B2-SUM(30*9)-25)=0;;SUM(B2-SUM(30*9)-25));IF(SUM(B2-SUM(30*9)-16)>0;SUM(B2-SUM(30*9)-16);))))& IF(SUM(B2-SUM(30*11))>=16;IF(SUM(B2-SUM(30*11))<25;16;IF(AND(SUM(B2-SUM(30*11))>=25;SUM(B2-SUM(30*11))<30);25;IF(SUM(B2-SUM(30*11))>=30;30;)))&"|";IF(SUM(B2-SUM(30*10)-30)>=0;IF(SUM(B2-SUM(30*10)-30)=0;;SUM(B2-SUM(30*10)-30));IF(SUM(B2-SUM(30*10)-25)>=0;IF(SUM(B2-SUM(30*10)-25)=0;;SUM(B2-SUM(30*10)-25));IF(SUM(B2-SUM(30*10)-16)>0;SUM(B2-SUM(30*10)-16);))));"|")

demo spreadsheet: https://docs.google.com/spreadsheets/d/

  • 1
    That is the longest excel formula I have ever seen! Thank you! – Datanovice Sep 11 '18 at 6:59
  • One final comment if I wanted to change the variables to a cell reference would that work with this formula? – Datanovice Sep 11 '18 at 7:08
  • 1
    @Datanovice yup, I don't see a reason why it would not – user0 Sep 11 '18 at 10:23
  • Thanks again! does the "|" represent an OR statement here? I'm trying to reproduce your code by having one variable and a max hour of 30 – Datanovice Sep 11 '18 at 15:30
  • 1
    @Datanovice hmm... this should help: i.imgur.com/Bh7ik7I.png & i.imgur.com/KldPeJx.png – user0 Sep 11 '18 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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