shorter expression of lookup formula

can you suggest me shorter expression of this lookup formula

Please Login or Register  to view this content.

the idea is that i have a list of numbers which have different equivalent value like:
14 = 7.5
15 = 8
16 = 9

and so on..

i count how many times in a row we have 14 and then look it up in a separate table "T_NS" and get their relative value and multiply it...
is there a shorter way to express this lookup, because the numbers that i need to lookup are approximately 100

You could create the following Function to cycle through them all and come up with the answer.

Ive put the starting numbers in an array string but you could easily change that to a range of cells etc

Please Login or Register  to view this content.

does this even work? I tried a shorter version and cannot get it to work. so according to your formula if you count the number of times 14 shows up in that section of row 9 you then want to multiply it by the value in T_NS so what if 14 appears 5 times and 15 appears 5 times, how does it differentiate? Could you post a small sample sheet with your expected results?

My understanding is that the 14, 15 etc have lookup values so its "how many times 14 appears multiplied by the lookup value relating to 14"

My understanding is that the 14, 15 etc have lookup values so its "how many times 14 appears multiplied by the lookup value relating to 14"

I get that but I still don't see how if 14 appears 5 times and so does 15 how it will differentiate. I'm interested in seeing how this progresses. I'd go with a helper column then the lookup but I have no ability to write vba as you do.

I think that theres potential for the helper columns to be a more sensible solution if Im being honest.

The lookup value for 14 was 7.5 while 15 was 8 therefore there would be a 2.5 difference if there were 5 of each (5 x 7.5 and 5 x 8) ....or have I misread the whole thing (easily believable!)

Hi,

To avoid us all guessing and wasting time, please upload a sample representative workbook and manually add the results you expect to see with notes as appropriate to explain your calculation.

Load this macro into a macro module

Please Login or Register  to view this content.

Then this formula will do what you ask for the numbers 3, 4, 5, 6

Formula:

Please Login or Register  to view this content.

this can be easily extended

Formula:

Please Login or Register  to view this content.

thank you all for the efforts and dedication - i appreciate it

the whole idea of the formula was to calculate the compensation hours for night shift
so i was counting how many times the employee took shifts from 8 o clock 9 10 and so on
then im looking up the shift number in another table to get how many hours night shift hours are worked and then multiply it by the respective number of shifts
then i divide it by 60 minutes so i can get the hours for compensation

i did this setup in the beginning, but then i found another setup by calculating the coefficient