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.
