Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using IF function .....
Really sorry if I have posted this in the wrong group (I can't seem to
post in the worksheetfunctions group. I am currently using the IF function to display a score value based on age. I am currently using 2 cells with 2 seperate equations for boys (with age in C5): =IF($C$5=11,10.6,IF($C$5=12,12.8,IF($C$5=13,15,IF( $C$5=14,17.2,IF($C $5=15,19.3,IF($C$5=16,20.1,IF($C$5=17,20.9,IF($C$5 =18,21.6)))))))) and girls (with age in B5): =IF($B$5=11,9,IF($B$5=12,10.6,IF($B$5=13,12.1,IF($ B$5=14,13.7,IF($B $5=15,15.4,IF($B$5=16,15.8,IF($B$5=17,16.3,IF($B$5 =18,16.7)))))))) I am trying to combine the 2 equations so that it will display based on entering age in C5 and M or F (for sex) in B5. I am pretty sure that I need to be using the AND / OR functions but can't seem to crack it. Any help (as always) much appreciated! Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using IF function .....
It would be much easier to manage and also more scalable to use a VLOOKUP()
function. On a separate sheet create a table with: _________ A________B________ C________D____ 1| 11 M =A1&B1 10.6 2| 12 M =A2&B2 12.8 3| 13 M =A3&B3 15 4| 14 M =A4&B4 17.2 5| 11 F =A5&B5 9 6| 12 F =A6&B6 10.6 7| 13 F =A7&B7 12.1 8| 14 F =A8&B8 13.7 etc... Then the formula would be =vlookup($c5&$b5,sheet2!$C:$D,2,0) " wrote: Really sorry if I have posted this in the wrong group (I can't seem to post in the worksheetfunctions group. I am currently using the IF function to display a score value based on age. I am currently using 2 cells with 2 seperate equations for boys (with age in C5): =IF($C$5=11,10.6,IF($C$5=12,12.8,IF($C$5=13,15,IF( $C$5=14,17.2,IF($C $5=15,19.3,IF($C$5=16,20.1,IF($C$5=17,20.9,IF($C$5 =18,21.6)))))))) and girls (with age in B5): =IF($B$5=11,9,IF($B$5=12,10.6,IF($B$5=13,12.1,IF($ B$5=14,13.7,IF($B $5=15,15.4,IF($B$5=16,15.8,IF($B$5=17,16.3,IF($B$5 =18,16.7)))))))) I am trying to combine the 2 equations so that it will display based on entering age in C5 and M or F (for sex) in B5. I am pretty sure that I need to be using the AND / OR functions but can't seem to crack it. Any help (as always) much appreciated! Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
User Function Question: Collect Condition in Dialog Box - But How toInsert into Function Equation? | Excel Programming | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |