Google-sheets – Match range to closest of 1 of 3 variables

formulasgoogle sheets

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/

Best Answer

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/

Related Topic