Excel Formula Question

[MartinLiss]

If I enter 1 in column A I'd like column B to increase by $20.50.
If I enter 2 in column A I'd like column B to increase by $9.70
If I enter 3 in column A I'd like column B to increase by $4.30
If I enter anything else in column A I'd like column B to decrease by $6.50

I assume I can't do that with a simple formula so how would I go about it?

[RobDog888]

You can do it with a complex nested If formula or some VBA code. Which would you prefer?

[westconn1]

i did some code for it

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Dim rn As Range
myr = Target.Row: myc = Target.Column + 1
Set rn = Me.Cells(myr, myc)
Select Case Target.Value
Case 1: myval = 20.5
Case 2: myval = 9.7
Case 3: myval = 4.3
Case Else: myval = -6.5
End Select
rn.Value = rn.Value + myval
Set rn = Nothing
End If

End Sub


but i never figured out how to be able to include a cells value in it's formula, a

pete

[Ecniv]

=IF(A2=1,B2+20.5,IF(A2=2,B2+9.7,IF(A2=3,B2+4.3,-6.5)))


I did it like this in another cell as you would need a starting number to begin with (to change).

:D :bigyello: :eek: :bigyello: :ehh: :thumb:

[Webtest]

Marty ...

If you haven't discovered it already, if you put your conditional calculation in a formula, you are going to bump up the "value in column B" EVERY TIME a recalculation is done. To check this out, hit <F9> a few times and see what happens. I would guess that you do NOT want that to happen. If you only want the action to occur 1 time it will take a VBA program, or you might want to think about adding a control to your worksheet.

[westconn1]

[/QUOTE]If you only want the action to occur 1 time it will take a VBA program, or you might want to think about adding a control to your worksheet.[QUOTE]

the code i posted only changes it once, untill you edit the code in column A

pete

[Webtest]

I'm guessing that it actually could be done in a formula in the worksheet cell, but it would be a hairy formula! Use the background color or text color as a flag in a complex IF chain ...

Which brings up the thought:

Does anyone know how to run a macro from a formula? There is a "CALL" function for external functions, but I couldn't get it to run a Macro ... sort of like the following:

=IF(logical_test, "Run Macro1", "Run Macro2")

Thanks for any and all comments, suggestions, and assistance.

[Webtest]

Marty ... are you still around?

It would be 'expensive' in processing time for a very large worksheet ... but if you really want to do it, here are some additional comments on Pete's approach:

In the Project Explorer, in "ThisWorkbook" module create a Public (Global) boolean flag (or an array of flags) and Initialize it, say to "False", in the Event handler "Private Sub Workbook_Open".

In the "Sheet" Module for the appropriate sheet, set up the Worksheet_Change event handler function. Everytime something on the sheet changes, the following code will run:Private Sub Worksheet_Change(ByVal Target As Range)
'TEST TEST TEST TEST
MsgBox("What changed? " & Target.Address) 'Show 'Target' operation
'END TEST
If Not myFlag Then
If Range("A1").Value > 0 Then
'Put your conditional calculations here
MsgBox ("Sheet Changed and A1 is Greater Than ZERO")
myFlag = True
End If
End If
End Sub
This is just a simple 'proof of concept' prototype to show that it can be done automatically ... without requiring the user to run a Macro ... if you really want to do it. It would get kind of hairy if you want to be able to "Undo" changes to your 'causal' variables. If you get stuck, I can get more explicit ...

[MartinLiss]

=IF(A2=1,B2+20.5,IF(A2=2,B2+9.7,IF(A2=3,B2+4.3,-6.5)))


I did it like this in another cell as you would need a starting number to begin with (to change).

:D :bigyello: :eek: :bigyello: :ehh: :thumb:Thanks, I didn't know you could do nesting in a formula. I put the following in column B (row 18). I changed the -6.5 part and it works.

=IF(A18=1,B17+20.5,IF(A18=2,B17+9.7,IF(A18=3,B17+4.3,B17-6.5)))

[RobDog888]

You guys are up early. :)

I didnt post any code last night because of the reasons posted. If you dont want the values to keep incrementing everytime any value is
changed then a second column will be needed.

Kind of like a 'Rate' range or sheet. Then in your formula for column 2 or "B" you can do something like this in the Formula bar...
Column A:
1,2,3 or ?

Column B:
Your numeric value
50 for ex.

Column C Formula:
=IF(A1=1,20.5,IF(A1=2,9.7,IF(A1=3,4.3,-6.5)))

Column D Formula:
=SUM(B3+C3)

Column A | Column B | Column C | Column D
3 50 4.3 54.3

[westconn1]

the routine i posted is in the code window for sheet1, in the worksheet change event, no other handlers or boolean were needed, for it to run each time a cell in column A was changed, any value in column B was incremented appropriately,
which was what was originally asked for, going back to a cell in column A would only increment it again if it was edited

but i understand now that it was the total value of column b you wanted to increment

pete

[RobDog888]

Yes I know it wasnt requested but I was reading between the lines. :)

Logically there would not be too much use for a condition where a value changes each time the rate value is changed. What if you made
a mistake in entering in a rate? Entered 1 and the value, say 50, was incremented by 20.50 to equal 70.50, but then you correct it to 2
and the value is incremented again to 80.20. Now the correct result without the mistake should be 59.70.

:)

[MartinLiss]

...but i understand now that it was the total value of column b you wanted to increment

peteThat's correct. Sorry I wasn't clear.

[MartinLiss]

Yes I know it wasnt requested but I was reading between the lines. :)

Logically there would not be too much use for a condition where a value changes each time the rate value is changed. What if you made
a mistake in entering in a rate? Entered 1 and the value, say 50, was incremented by 20.50 to equal 70.50, but then you correct it to 2
and the value is incremented again to 80.20. Now the correct result without the mistake should be 59.70.

:)This is a spreadsheet that I use to track my online poker winnings from little (one-table) tournaments. The cost to enter is $6.50 and the prizes are: 1st = $27, 2nd = $16.20 and 3rd = $10.80. So each time I create a new row with the place I finished in column A, column B is incremented (usually :)).

As I indicated I have what I need.

[Webtest]

Pete ... Your lead was GREAT! I just added the boolean as a tool to play with in situations like RobDog and I pointed out ... the "Oops!" case ... where you might want to go back and change a previously set value in Column A. It was just a quick exploration.

[Webtest]

Martin:

Another way to do this is to use the VLOOKUP function. Just make a table:

1 20.50
2 9.70
3 4.30
4 -6.50

If the range of this array is say D1:E4 then in cell B1:

=VLOOKUP(A1,$D$1:$E$4,2,TRUE)

Now it is trivial to change the rewards table. Just a thought.

[RobDog888]

Art, the VLOOKUP using the additional range is the same logic as I suggested in adding a colum for the 'Rate'. Either
one will work well. :thumb:

[BazP]

Use hidden columns and base your formulas on those hidden amounts. Or use the "scenario" function of Excel