excel formula - possibly visual basic method

[David]

I need to set up a quite a complicated spread sheet. This is the senario: say
my sales figure for monday is £50 (which is my target). However my actual
sales figure for monday is £40. How do i get the cell to show the difference
between my forecast(target) and my actual having predefined the cell with my
target sales figure so that when i type the actual sales figure in it will
automatically show the difference(+/-). How do i predefine the cell with the
forecast sales figure for a cell?

In addition to this i would like to know how if i hit the sales target do i
get the cell to show green. And if the sales target is missed how do i get
the cell to show red without having to change the cell each time.

What hopefully should happen is that as in the above scenario if for the
monday i typed in the actual sales figure as £40, the cell will hopefully
show -£10 with the cell baground turning red. If for example i typed in £60
for monday it would show +£10 and the cell would turn green. I hope to repeat
this process for the rest of the week with different forecast figures! Many
thanks

 

[Marcelo]

hi David,

assuming that you have the actual figures on the B column, the forecast
figures on the C column and the variance on the D column. you can hide the
forecast column if it's necessary for your proposal.

on D column use the formula
=b2-c2

and use a conditional format to set the cell red or green:
click on format menu chose conditional format, on the drop down chose bigger
than - on the box type 0 format as you need color, font color, borders
add an new condition chose lower than - on the box type 0


hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"David" escreveu:

 

[Gord Dibben]

David

Without VBA you can have a value or a formula in a cell, not both at the same
time.

I would stick my target number in a cell, say E1

In A1 I would enter the Monday sales figure of 40

In B1 I would enter =A1-E1

Select B1 and Format>Conditional Formatting>Condition1>Formula is =B1<E1
format to red

Add>Condition2>Formula is =B1>=E1 format to green.

These formats can be copied to other cells by using the formatting paintbrush.

If you wnat to do it all in one cell event code is your friend.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
n = Target.Row
If Excel.Range("B" & n).Value <> "" Then
Excel.Range("B" & n).Value = Excel.Range("B" & n).Value _
- Excel.Range("E1")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

E1 contains your daily target.

Column B is where you enter the daily sales figures.

Also place the CF on column B cells.


Gord Dibben MS Excel MVP