[Year 12 IPM] Help Request from Excel Experts please.
Keith Richardson
keithcr at fastmail.fm
Fri Apr 1 17:45:55 EST 2005
Many thanks Craig - you have vastly exceeded my current excel level, so
I enjoy the journey of discovery!
Regards, Keith
On Fri, 1 Apr 2005 11:43:41 +1000, "Craig Richardson"
<crichardson at fhs.vic.edu.au> said:
> Hi Keith,
>
> To solve the problem with one formula, the quickest solution is to use
> an array formula. This is a slightly different method than using a
> normal formula, but it allows you to run an individual function over a
> range of cells.
>
> If you have a range of numbers (say C2:C4) that you wish to round, and
> then sum in one command, type the command
> =SUM(ROUND(C2:C4,0))
> and instead of pressing ENTER, press CONTROL-SHIFT-ENTER. This will
> enter the formula appear but with curly braces around it.
> {=SUM(ROUND(C2:C4,0))}
>
> The end result is that the ROUND function will be applied on each cell
> in that range, and then the SUM function will be applied across the
> results.
>
> I have attached a quick example in Excel
>
> Hope this helps,
> Craig
>
> Craig Richardson
> Frankston High School
> crichardson at fhs.vic.edu.au
>
> -----Original Message-----
> From: ipm-bounces at edulists.com.au [mailto:ipm-bounces at edulists.com.au]
> On Behalf Of Keith Richardson
> Sent: Thursday, 31 March 2005 12:56 PM
> To: List IP&M
> Subject: [Year 12 IPM] Help Request from Excel Experts please.
>
> Hi - I have received an Excel query from a workmate that has me stumped,
> and I am wondering if anyone out there in expertsville has an answer.
> Below please find the email he sent me.
> What he wants is for excel to automatically drop of the cents retaining
> only the dollars, and for this new dollar-only value to be fixed (not
> just having the cents invisible but really still there).
> Many thanks, Keith.
>
>
>
> On Wed, 30 Mar 2005 12:05:50 +1000, "Leonard Hain"
> <len.hain at yavneh.vic.edu.au> said:
> > Keith
> >
> > Further to the query I raised with you the other day regarding the
> > identification of a formula that can be used when adding a row of
> figures
> > in
> > an excel worksheet, where figures after the decimal point have been
> > rounded
> > off to disclose whole numbers but to still appear if the sum total at
> the
> > end of the column adds. This is for presentation purposes, where you
> > don't
> > want to explain rounding differences and there are too many line items
> to
> > manually address the problem.
> >
> > Using the standard SUM formula or the ROUND formula. you achieve
> results
> > in
> > the simplest form that look like they don't add, such as 2+2=3. The
> > following example demonstrates.
> >
> >
> > Actual Figure
> > Elimination of Decimals
> >
> > 1.51
> > 2
> >
> > 1.51
> > 2
> >
> > 3.02
> > 3
> >
> > Sum
> > Sum
> >
> >
> >
> > Can you please advise of an addition formula that addresses this
> issue.
> >
> > Leonard Hain
> > Financial Controller
> > Mizrachi Nominees Pty Ltd and
> > Yavneh College Nominees Pty Ltd
> >
> > Postal Address : P.O. Box 164, South Caulfield 3162
> > Local Address : 2 Nagle Avenue, Elsternwick 3185
> > Direct Phone : 61 3 9523-8106
> > Facsimile : 61 3 9523-7621
> > Email : len.hain at yavneh.vic.edu.au
> >
> >
> >
> >
> >
> >
> Keith Richardson
> Leibler Yavneh College
> Elsternwick Ph (03)9528 4911
> keithcr at fastmail.fm
>
> _______________________________________________
> ipm mailing list
> ipm at edulists.com.au
> http://www.edulists.com.au/mailman/listinfo/ipm
Keith Richardson
Leibler Yavneh College
Elsternwick Ph (03)9528 4911
keithcr at fastmail.fm
More information about the ipm
mailing list