Excell: How to code formula based on contents of another cell.

[azzi2000]

This should be simple.
I am trying to get formula in one cell based on the contents of a column (same row)

ColumnA, ColumnB, ColumnC, ColumnD(get formula)
---------------------------------------------------------
Score A , 5 , 1.1 , NewFormula
Score A , 5.6 , 2.2 , NewFormula
Score B, 5.9 , 2.5 , NewFormula
Score C, 6.5 , 2.65 , NewFormula

How do I prog the following:
NewFormula:
Select Case columnA 'based on column A
Case 'Score A'
ColumnB*0.05 + ColumnC
Case 'Score B'
ColumnB*0.1 + ColumnC*1.2
Case 'Score C'
ColumnB*0.15 + COlumnC*1.05

Please advice,
Thank you.
Dré

 

[jebry]

Hi Dre!

You use a nested IIf statement:

IIf(A1="Score A", ColumnB*0.05 + ColumnC, IIf(A1="Score B", ColumnB*0.1 + ColumnC*1.2, IIf(A1="Score C", ColumnB*0.15 + COlumnC*1.05, etc)))

Your last else should be some default formula if there is one.

Also, there is a limit on how many IIf statements you can nest. I am not sure how many that is, but if you reach that limit, let us know a maybe another solution can be found.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[Gavona]

You can do this with nested if statements.

=if(condition,value_if_true,value_if_false)

=IF(A4="score A",B4*0.05+C4,IF(A4="score b",B4*0.1+C4*1.2,IF(A4="score c",B4*0.15+C4*1.05,"unexpected score!")))

If you want to use code then look up "Using Select Case" in help. This would do it:

Function mycalc(score, columnB, columnC)
Select Case score  'based on column A
Case "Score A"
       mycalc = columnB * 0.05 + columnC
Case "Score B"
       mycalc = columnB * 0.1 + columnC * 1.2
Case "Score C"
       mycalc = columnB * 0.15 + columnC * 1.05
Case Else
       mycalc = "unexpected score"
End Select
End Function

Thanks,

Gavin

 

[azzi2000]

Thank you for your reply, however how can I make it reference to the current row and cell since the formula will change as we go along next row, next column...
let say we are at row 10
how can i refenrece the formula to cuurent row10, column1.
Please advice,
Thank you.
Dré

 

[xlbo]

By not using absolute referencing (the $ around cell addresses), the rows will increment by themselves

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[Zathras]

Look it up in the help file. Try the topic "Move or copy a formula" or "About cell and range references"

We'll be glad to help you with specific problems, but this is not a general Excel help desk. You are expected to do a little work on your own.

 

[AnotherHiggins]

azzi2000,

You're making this harder than it is. As an example, try this:
[ul][li]Going down column A, type in 1, 2, 3[/li]
[li]In B1, type in [COLOR=blue white]=A1[/color][/li]
[li]hover the mouse over the bottom right corner of cell B1 until the cursor changes to a small black +[/li]
[li]click and drag down to B3[/li][/ul]
You will notice that the cell reference is automatically relative. As xlbo and Zathras point out, you can find out about relative vs. absolute references in Excel's help file.

PS jebry: the limit for nested IFs is 7.

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

John - can get around that by not nesting eg

=IF(A1=test,"A","") & IF(A1=test2,"B","") & IF(A1=test3 etc etc ad nauseum

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[azzi2000]

Let me rephrase this. (I know how to do copy, paste, expand... using excel) But Need the formula inside macro to get:
CurrentRow, currentColumn
and reference formula to currentRow, column1
Please advice,
tahnk you.
Dré

 

[AnotherHiggins]

Geoff,

I know.... I learned that from you long ago! I was just replying to

Also, there is a limit on how many IIf statements you can nest. I am not sure how many that is...

But you're right - I should have gone further and told him/her how to get around that limit. For that matter, it is worth noting that in Excel, it's IF with a single I.

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

John - LOL - fair enough

Dré - if you wanted a code answer - you should've posted in the VBA forum (Forum707). However - if you have this in code:

strForm = "=A1+10"
Range("B1:B50").formula = strForm

excel is "smart" enough to do all that for you - a bit of testing woul've shown you that. As Zathras has already stated, this is not a general Excel help desk. You are expected to do a little work on your own.

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[azzi2000]

oops sorry about that.
Thank you anyways...
Dré