Formula to return a formula
in [General]
|
Prev: How to stop Excel from automatically truncating leading zeros
Next: Insert a picture into an excel cell & have it stay there
From: Pelldom on 13 Apr 2010 17:52 At least I think that's what I'm looking for. here's my situation: I've got a sheet where there is a variable (X) that changes depending on what row it is on. So I have a column (column c) that lists these variables (X is dependant on things from a different sheet). X is a muliplier that is utilized differently depending on the value of N. N is a Picklist selection and can change periodically. Or even be duplicated on more than one row. I have a vlookup that checks the value of N (column a) and needs to return a formula that has X applied if needed on the appropriate row (column b). Example: for cells c3, c4, c5, X= 2, 3, 7 respectively. If N="age", then I need 5*X If N="both", then I need X/2 If N="cans", then I need 20/X+1 Row 3: if N (a3) is "age" b3 should return (and calculate) 5*2 if N (a3) is "both" b3 should return (and calculate) 2/2 if N (a3) is "cans" b3 should return (and calculate) 20/2+1 Row 4: a4 is "age"; b4 should be 5*3 a4 is "both"; b4 should be 3/2 a4 is "cans"; b4 should be 20/3+1 Row 5: a5 is "age"; b5 should be 5*7 a5 is "both"; b5 should be 7/2 a5 is "cans"; b5 should be 20/7+1 This is looking fairly complex so I hope my question is understandable. I've been fighting with this for days now and have exhausted every How-To and help file I can find on Excel. I'm hoping someone can help with this.
From: ck13 on 13 Apr 2010 21:23
Hope i have understand what you trying to say. In B3, copy this formula =IF(A3="both",C3/2,IF(A3="age",5*C3,20/C3+1)) drag this down to B5. "Pelldom" wrote: > At least I think that's what I'm looking for. here's my situation: > I've got a sheet where there is a variable (X) that changes depending on > what row it is on. So I have a column (column c) that lists these variables > (X is dependant on things from a different sheet). > > X is a muliplier that is utilized differently depending on the value of N. N > is a Picklist selection and can change periodically. Or even be duplicated on > more than one row. > I have a vlookup that checks the value of N (column a) and needs to return a > formula that has X applied if needed on the appropriate row (column b). > > Example: > for cells c3, c4, c5, X= 2, 3, 7 respectively. > If N="age", then I need 5*X > If N="both", then I need X/2 > If N="cans", then I need 20/X+1 > > Row 3: > if N (a3) is "age" b3 should return (and calculate) 5*2 > if N (a3) is "both" b3 should return (and calculate) 2/2 > if N (a3) is "cans" b3 should return (and calculate) 20/2+1 > > Row 4: > a4 is "age"; b4 should be 5*3 > a4 is "both"; b4 should be 3/2 > a4 is "cans"; b4 should be 20/3+1 > > Row 5: > a5 is "age"; b5 should be 5*7 > a5 is "both"; b5 should be 7/2 > a5 is "cans"; b5 should be 20/7+1 > > This is looking fairly complex so I hope my question is understandable. I've > been fighting with this for days now and have exhausted every How-To and help > file I can find on Excel. I'm hoping someone can help with this. |