[Year 12 IPM] Help Request from Excel Experts please.

[Craig Richardson]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Array.xls
Type: application/vnd.ms-excel
Size: 19456 bytes
Desc: Array.xls
Url : http://www.edulists.com.au/pipermail/ipm/attachments/20050401/6fb40136/Array-0001.xls