MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Two questions from a rank amateur


Posted by Doug on February 12, 2002 8:02 AM

I seldom if ever use Excel, so please excuse my ignorance...

1. How do I get a cell to drop all numbers after the
tenth spot without rounding up or down, i.e,get
14.76 to show as 14.7?

2. How do I get a formula to stay focused on data in
a particular cell, basically awaiting new data, vs.
referring to the data in the cell once it's
moved? If I cut and paste cell data to a new
location, I want the formula that refers to the
original cell location to calculate new data to be
entered there.

I hope I've made some semblence of sense here...Thanks


Posted by Mark W. on February 12, 2002 8:12 AM

1. =TRUNC(14.76,1)
2. Confused by your description. Care to elaborate?

Posted by Doug on February 12, 2002 8:21 AM

2. If a cell @ I4 has a formula
=SUM(F4-G4)*113/H4 how can I cut and paste
F4, G4, and H4 to a new location, say F,G,
& H17, and have the formula still refer to
F4, G4, H4 - new data to be inputed - and not
still calculating the old data now at F, G, &
H17?

Thanks for the help on question #1.

Posted by Mark W. on February 12, 2002 8:38 AM

=SUM(INDIRECT("F4")-INDIRECT("G4"))*113/INDIRECT("H4") ...

...and BTW, the SUM() function is superfluous.

Use...

=(INDIRECT("F4")-INDIRECT("G4"))*113/INDIRECT("H4")

Posted by Doug on February 12, 2002 9:14 AM

Thanks, and while we're at it...

I've got 20 formula's in I4 down through I23 that have generated values for me...Can I get excel to pick out the lowest 10 of the 20 values and put them into the next column (J), leaving cells in J that correspond to the highest ten values in I blank?

Posted by Mark W. on February 12, 2002 9:29 AM

You could...

...enter the array formula...

{=IF(OR(I4=SMALL($I$4:$I$23,{1,2,3,4,5,6,7,8,9,10})),I4,"")}

...into J4 and copy down to J23. Technically, this
doesn't leave a blank cell, but from an appearance
standpoint it's the closest thing.

Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.

Posted by Doug on February 12, 2002 10:44 AM

Re: You could...

I've cc'd the formula (less the {} brackets intoJ4 through J23...I don't understand the next part re the Control+Alt+Enter keys??? Obviously it's not working yet. Thanks for your help.

...enter the array formula... {=IF(OR(I4=SMALL($I$4:$I$23,{1,2,3,4,5,6,7,8,9,10})),I4,"")} ...into J4 and copy down to J23. Technically, this


Posted by Doug on February 12, 2002 10:58 AM

Even better...What I actually am looking to do...

is add the lowest ten of the twenty values from J4 through J23, and muliply that sum by .096 to arrive at a figure (formula for a USGA handicap).

I don't necessarily have to address each J4 through J23 lowest ten vs. highest ten if I can direct one cell to do the entire calculation.

...enter the array formula... {=IF(OR(I4=SMALL($I$4:$I$23,{1,2,3,4,5,6,7,8,9,10})),I4,"")} ...into J4 and copy down to J23. Technically, this


Posted by Mark W. on February 12, 2002 11:49 AM

Re: Even better...What I actually am looking to do...

ERR

Posted by Mark W. on February 12, 2002 11:49 AM

Re: Even better...What I actually am looking to do...

ERR

Posted by Mark W. on February 12, 2002 11:50 AM

Re: You could...

Control+Shift+Enter. See the Excel Help topic for
"About array formulas and how to enter them". I've cc'd the formula (less the {} brackets intoJ4 through J23...I don't understand the next part re the Control+Alt+Enter keys??? Obviously it's not working yet. Thanks for your help.