Selecting and applying the correct forumla

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
 
P

Pete_UK

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
 
A

Arjay

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

.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top