Hi guys,
I love excel, as an amateur I drive my wife mad with new excel projects however I think I have hit a wall with my skill so I thought I would ask the proper gurus
I have a fixed set of 7 data groups: H1, N1, A1, B1, G1, L1, S1
Each group has a fix number of 5 variable numbers applied to it for example:
H1-1,5,10,15,12
N1-2,6,8,10,12
A1-8,10,5,7,16
B1- etc
There are many iterations of each data group ie
A1-2,3,4,5,6
A2-4,7,2,8,3
A3-6,9,4,2,3
A4-4,7,9,3,4
The total number of iterations is unknown; the number of iterations are variable between the data groups ie A1-A25, H1-H8, B1-B2,N1-N10; and will change in number
Then using below as sample data
H1-8,7,10,15,12
H2-1,5,12,15,19
H3-1,8,14,10,11
N1-2,6,8,10,18
N2-2,9,11,10,13
A1-8,10,5,7,16
B1-10,12,11,13,9
G1-11,13,15,1,2
L1-12,10,5,6,6
S1-10,12,14,15,6
I want to find the sum of each of the 5 numbers across each data group, ie
H1,8+N1,2+A1,8+B1,10+G1,11+L1,12+S1,10 = 61
H1,7+N1,6+A1,10+B1,12+G1,13+L1,10+A1,12 = 70
H1,10+N1,8+A1,5+B1,11+G1,15+L1,5+S1,14 = 68
H1,15+N1,10+A1,7+B1,13+G1,1+L1,6+S1,15 = 67
H1,12+N1,18+A1,6+B1,9+G1,2+L1,6+S1,6 = 59
this is for all the "1" iterations
and then do the same for each possible iteration across all data groups - this is where the number of calculations become a huge ie
H1+N1+A1+B1+G1+L1+S1
H1+N1+A1+B1+G1+L1+S2
H1+N1+A1+B1+G1+L1+S3
H1+N1+A1+B1+G1+L2+S1
H1+N1+A1+B1+G1+L2+S2
H1+N1+A1+B1+G1+L2+S3
H1+N1+A1+B1+G1+L3+S1
H1+N1+A1+B1+G1+L3+S2
H1+N1+A1+B1+G1+L3+S3
Etc etc
Finally what I am actually looking for is where each set of variable numbers in each data group across all iterations like the above or:
H1,8+N1,2+A1,8+B1,10+G1,11+L1,12+S1,10 = 61
H1,7+N1,6+A1,10+B1,12+G1,13+L1,10+A1,12 = 70
H1,10+N1,8+A1,5+B1,11+G1,15+L1,5+S1,14 = 68
H1,15+N1,10+A1,7+B1,13+G1,1+L1,6+S1,15 = 67
H1,12+N1,18+A1,6+B1,9+G1,2+L1,6+S1,6 = 59
is the closest to 70 (or a number of my choice)
ie if they all add up to 70 that would be perfect
4 out of 5 add up to 70 almost perfect
2 out of 5 add up to 70 rubbish
so in the above example would be rubbish because only 1 row adds up to 70
it would then rank which combination of data groups are the best for producing 70 across all rows
This has confused the hell out of me just typing it out so i have tried to replicate it as closely as i can in excel and attached it, but you can see from the math that the number of calculation required is huge especially when you consider the number of iterations of each data group can grow in number
I know there must be a more simple way of doing this and after all isn't this what computers are for, complex calculations? lol
Please help me with my puzzle
Thanks
Gordon
Bookmarks