+ Reply to Thread
Results 1 to 5 of 5

Reference to calculate for multiple cells

  1. #1
    Registered User
    Join Date
    12-03-2010
    Location
    Colorado Springs, CO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Reference to calculate for multiple cells

    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,"")

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Reference to calculate for multiple cells

    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?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    12-03-2010
    Location
    Colorado Springs, CO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Reference to calculate for multiple cells

    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))))))))))

  4. #4
    Registered User
    Join Date
    12-03-2010
    Location
    Colorado Springs, CO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Reference to calculate for multiple cells

    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.

  5. #5
    Registered User
    Join Date
    12-03-2010
    Location
    Colorado Springs, CO
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Reference to calculate for multiple cells

    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)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1