Selecting and applying the correct forumla

  • Thread starter Thread starter Arjay
  • Start date Start date
A

Arjay

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
 
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
 
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 said:
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

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

.
 
Back
Top