Help with a formula

[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

 

[DDM]

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

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

 

[JEM]

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

 

[JEM]

Thanks guys, I'll try both and see what happens...

 

[JEM]

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