Reference to calculate for multiple cells

[Scuba-Steve]

Help me fill in the ?__?s and make a working formula. This is going to be used a a rather large set of data that I do not want to have to repeat over and over in column C.


1: Name , Raw Score , Points Awarded
2: Tom , 1 , =A6
3: Mike , 2 , =A6
4: Jen , 3 , =A6
5:
6:=if(B?Row?=1,10,if(B?Row?=2,31,if(B?Row?=3,84,"")

[Ron Coderre]

Try approaching from a different direction...Using your posted scenario:

Please Login or Register  to view this content.

Copy that formula down through C4

Is that something you can work with?

[Scuba-Steve]

Really I tried to simplify this as much as possible. The formula I am using is huge.

Here is one out of 6 areas that I am working with. Currently each person has to have this formula along with 5 others that are just as large but cover other areas. Ideally I would like to place this formula in once in another sheet, and have the 140 or so people all feed off of the one entry.

I12: Enter Raw score

J12: =IF(I12="","",IF(I12="Profile",60,IF(BD12="Male",BE12,IF(BD12="Female",BP12,"Data Error"))))

BA12: Enter Birthday

BC12: =INT((TODAY()-BA12)/365.25)

BD12: Enter Gender

BE12: =IF(BC12<22,BF12,IF(BC12<27,BG12,IF(BC12<32,BH12,IF(BC28<37,BI12,IF(bc<42,BJ12,IF(BC28<47,BK12,IF(BC28<52,BL12,IF(BC28<57,BM12,IF(BC28<62,BN12,BO12)))))))))

BF12: =IF(I12=0,0,IF(I12=1,3,IF(I12<4,I12+3,IF(I12<7,I12+4,IF(I12<10,I12+5,IF(I12<12,I12+6,IF(I12<15,I12+7,IF(I12<17,I12+8,IF(I12<20,I12+9,IF(I12<23,I12+10,IF(I12<25,I12+11,IF(I12<28,I12+12,IF(I12<31,I12+13,IF(I12<33,I12+14,IF(I12<36,I12+15,IF(I12<39,I12+16,IF(I12<41,ill+17,IF(I12<44,I12+18,IF(I12<46,I12+19,IF(I12<49,I12+20,IF(I12<52,I12+21,IF(I12<54,I12+22,IF(I12<57,I12+23,IF(I12<60,I12+24,IF(I12<62,I12+25,IF(I12<65,I12+26,IF(I12<68,I12+27,IF(I12<70,I12+28,IF(I12<72,I12+29,100)))))))))))))))))))))))))))))

BG12: =IF(I12=0,0,IF(I12<9,I12+15,IF(I12<16,I12+16,IF(I12<23,I12+17,IF(I12<30,I12+18,IF(I12<37,I12+19,IF(I12<44,I12+20,IF(I12<51,I12+21,IF(I12<58,I12+22,IF(I12<65,I12+23,IF(I12<72,I12+24,IF(I12<76,I12+25,100))))))))))))

BH12: =IF(I12=0,0,IF(I12<11,I12+19,IF(I12<31,I12+20,IF(I12<49,I12+21,IF(I12<68,I12+22,IF(I12<78,I12+23,100))))))

BI12: =IF(I12=0,0,IF(I12<17,I12+23,IF(I12<56,I12+24,IF(I12<76,I12+25,100))))

BJ12: =IF(I12=0,0,IF(I12<15,I12+25,IF(I12<54,I12+26,IF(I12<74,I12+26,100))))

BK12: =IF(I12<5,0,IF(I12<8,I12+27,IF(I12<17,I12+28,IF(I12<26,I12+29,IF(I12<35,I12+30,IF(I12<44,I12+31,IF(I12<53,I12+32,IF(I12<62,I12+33,IF(I12<67,I12+34,100)))))))))

BL12: =IF(I12<5,0,IF(I12=5,36,IF(I12<11,I12+32,IF(I12<17,I12+33,IF(I12<23,I12+34,IF(I12<28,I12+35,IF(I12<34,I12+36,IF(I12<40,I12+37,IF(I12<45,I12+38,IF(I12<51,I12+39,IF(I12<57,I12+40,IF(I12<60,I12+41,100))))))))))))

BM12: =IF(I12<5,0,IF(I12<7,I12+38,IF(I12<16,I12+39,IF(I12<25,I12+40,IF(I12<34,I12+41,IF(I12<43,I12+42,IF(I12<52,I12+43,IF(I12<57,I12+44,100))))))))

BN12: =IF(I12<5,0,IF(I12<8,I12+40,IF(I12<15,I12+41,IF(I12<22,I12+42,IF(I12<29,I12+43,IF(I12<36,I12+44,IF(I12<43,I12+45,IF(I12<50,I12+46,IF(I12<54,I12+47,100)))))))))

BO12: =IF(I12<5,0,IF(I12<8,I12+42,IF(I12<14,I12+43,IF(I12<19,I12+44,IF(I12<25,I12+45,IF(I12<31,I12+46,IF(I12<36,I12+48,IF(I12<41,I12+49,IF(I12<48,I12+50,IF(I12<51,I12+51,100))))))))))

[Scuba-Steve]

I guess I should break this down a little as well. This is used to calculate a pushup score for the army for males. I need to incorperate situps and run as well as build a second section for females.

I12: Raw score

J12: Where the Points are actually displayed (Checks BD12 for gender, sends to BE12)

BA12: Birthdate

BC12: Age Checks BA12 and calculates Age

BD12: Gender

BE12: Checks age for Males, sends to appropriate age bracket

BF12:B012 Age Brackets coinciding with raw scores and points.

[Scuba-Steve]

I just wanted to inform, that I figured it out on my own. I am completely self taught, so if I have not used a function before I do not know how to use it until I play around with it. I solved this problem using a table and a vlookup.
Raw Score, Points earned
1: 1, 5
2: 2, 12
3: 3, 19
4:name, raw, points
5:person 1, 2, =VLOOKUP(B5,A2:B4,2)
6:person 2, 3,=VLOOKUP(B6,A2:B4,2)