Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting and applying the correct forumla
Hi
How do I get Excel to apply a certain formula (held in a central table) based on particular values in the same row? I don't want the actual result of the formula whilst it's in the central table, as this will be different when pasted into each row. Example, (where Col D is the place I want the formula from the central table to be applied to): Central table: [Row 1] = A1+B1 (Apply this if column C equals the word "Rule 1") [Row 2] = A1-B1 (Apply this if column C equals the word "Rule 2") Spreadsheet: Col A Col B Col C Col D 1 1 Rule 1 2 2 2 Rule 2 0 Thanks Arjay |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting and applying the correct forumla
If you only have two rules then you could have something like this in
D1: =IF(C1="","",IF(C1="Rule 1",A1+B1,IF(C1="Rule 2",A1-B1,""))) then copy down. If you have more rules then you will have to give us some more information about what you have in your "central table". Hope this helps. Pete On Mar 4, 11:21*am, Arjay wrote: Hi How do I get Excel to apply a certain formula (held in a central table) based on particular values in the same row? *I don't want the actual result of the formula whilst it's in the central table, as this will be different when pasted into each row. Example, (where Col D is the place I want the formula from the central table to be applied to): Central table: [Row 1] = A1+B1 (Apply this if column C equals the word "Rule 1") [Row 2] = A1-B1 (Apply this if column C equals the word "Rule 2") Spreadsheet: Col A * Col B * Col C * * * * * Col D 1 * * * 1 * * * Rule 1 * * * * *2 2 * * * 2 * * * Rule 2 * * * * *0 Thanks Arjay |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Selecting and applying the correct forumla
It's a bit more complicated than that as the central table is already big and
will grow (I already have 30+ formulas, so cannot use 'if' statements), and depending on the value in col C, the formula may be a totally different. All formulas are aimed at creating a string on text in Col D, much like a mail merge. Here's my real life problem (Remember: Here I can easily bash in value for columns A, B, and C. I want column D to be generated by applying the correct formula chosen from the central table) Central Table Col A Col B Magazine Group Test =[Col C]&": "&"[Col B]&"/5 Stars" Standalone Star rating =[Col B]&"/5 Stars" Input Spreadsheet Col A Col B Col C Col D [Award] [Input Value 1] [Input Value 2] [Award Text] Magazine Group Test 4 SLR Cameras SLR Cameras: 4/5 Stars Standalone Star rating 3 3/5 Stars Standalone Star rating 4 4/5 Stars Magazine Group Test 4 Digital Cameras SLR Cameras: 4/5 Stars Magazine Group Test 5 Digital Cameras SLR Cameras: 5/5 Stars Because I need to be ensure the output in col D reads perfectly, and I'm processing several hundreds of these a day, I am hoping a formula "lookup and apply" will be easier than writing out the Col D text for each one manually! "Pete_UK" wrote: If you only have two rules then you could have something like this in D1: =IF(C1="","",IF(C1="Rule 1",A1+B1,IF(C1="Rule 2",A1-B1,""))) then copy down. If you have more rules then you will have to give us some more information about what you have in your "central table". Hope this helps. Pete On Mar 4, 11:21 am, Arjay wrote: Hi How do I get Excel to apply a certain formula (held in a central table) based on particular values in the same row? I don't want the actual result of the formula whilst it's in the central table, as this will be different when pasted into each row. Example, (where Col D is the place I want the formula from the central table to be applied to): Central table: [Row 1] = A1+B1 (Apply this if column C equals the word "Rule 1") [Row 2] = A1-B1 (Apply this if column C equals the word "Rule 2") Spreadsheet: Col A Col B Col C Col D 1 1 Rule 1 2 2 2 Rule 2 0 Thanks Arjay . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need urgent help selecting correct test!! | Excel Worksheet Functions | |||
if then forumla | Excel Worksheet Functions | |||
Selecting the correct number from a range of cells | Excel Worksheet Functions | |||
Forumla Help | Excel Discussion (Misc queries) | |||
Forumla Help | Excel Discussion (Misc queries) |