Help with a formula

  • Thread starter Thread starter JEM
  • Start date Start date
J

JEM

Maximum value for A1 is 72.

Any number can be entered in A1

When a number higher than 72 is entered in A1, 72 appears in A1, and the
remainder is carried over into B1.

Ex: Enter 50
A1: 50
B1: 0

Ex: Enter 72
A1: 72
B1: 0

Ex: Enter 73
A1: 72
B1: 1

Ex: Enter 80
A1: 72
B1: 8
 
Almost!
That formula works fine, for example, when I enter '84' in A1, 12 appears in
B1. But, 84 is still in A1.

What I'd like to do is the following: I want the user to enter data in A1,
regradless of value and have the formula split the value between A1 and B1
when necessary.

So, using the example above, when I enter 84 in A1, 72 appears in A1 and 12
appears in B1.

Now when entering data, users have to do this manually - flag any number
higher than 72, enter 72 in A1, and the remainder in B1.

Thanks,

JEM

From: "DDM" <[email protected]>
Subject: Re: Help with a formula
Date: Wednesday, April 21, 2004 12:54 PM

JEM, here ya go. In B1: =MAX(A1-72,0)

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
 
Sorry I misunderstood, JEM, but now we have another problem. Perhaps someone
who knows the code can post a VBA procedure that will allow you to do what
you describe for Cell A1; there is no way to do it that does not involve
VBA.

As a workaround, you could set up a helper cell somewhere where the user
would enter a value. Then you could put this in Cell A1: =MIN(72,[address of
helper cell]), where you substitute the address of the helper cell for
what's in brackets. Your formula for B1, then, would be: =MAX([address of
helper cell]-72,0). That will give you the result you're looking for in A1
and B1.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
 
Hi
this would require VBA (using an event procedure: see:
http://www.cpearson.com/excel/events.htm)

Put the following code in your worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value > 72 Then
.Offset(0, 1).Value = .Value - 72
.Value = 72
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub
 
DDM,

Thanks! I've decided to go with the formula you suggested as it will be less
confusing for users.

I have a few questions though:

Here's a sample of the formula with results:

Total 72 Over
90 72 18
84 72 12
55 55 0
0 0 0
72 0
55 55 0
44 44 0
0 0 0
0 0 0
0 0 0


Before I enter any data, the sheet looks fine, and the first time I enter
data, it's also fine, however, if I delete an entry in the 'Total' column,
the value changes to '72' in the '72' column. (notice ROW 6 COL A &B) Also,
when copying the formula, (select, control D) the cells fill in with '0'.

Two questions:
1. If a number is deleted in the total column, how can I get the '72' column
to revert back to a blank cell?

2. When copying the formula, how can I get '0' not to show, i.e., only a
value higher than one shows?

Thanks,

JEM
 
Back
Top