Rounding up?

[scriblesvurt]

Hello,

I have a sheet that splits a number up over several collums by a percentage then adds data from other collums.

The percentage splits, which are decimals all add up to 100 when summed, however, the total of the number when split is short a few decimal places. I think this may have something to do with rounding up.

Can anyone make anysence of this??

=IF((($AF41*FL$4)-($AF41*FL$4))>0.2499,CEILING(($AF41*FL$4),0.5),IF(($AF41*FL$4)>0,($AF41*FL$4),0))

Thanks,

Sam

[K Dales]

I will try to translate the formula into English:
Take the number in AF41 and multiply it by what is in FL4. Let's call that
X. If X minus itself is greater than 0.2499, round it to the nearest 0.5.
Otherwise, if it is greater than zero keep it as it is; if it is zero or less
make it zero.

One thing seems odd right from the start: ($AF41*FL$4)-($AF41*FL$4) is
always going to be zero. Granted, rounding may make it "off" a little, but
never by as much as 0.2499, so the first part of this IF statement seems to
be non-functional to me. The CEILING part of the IF statement will never be
invoked. So I think there is something wrong here. But without knowing the
details of how you need to split your number it is hard to know how it needs
to be fixed.
--
- K Dales


"scriblesvurt" wrote:

>
> Hello,
>
> I have a sheet that splits a number up over several collums by a
> percentage then adds data from other collums.
>
> The percentage splits, which are decimals all add up to 100 when
> summed, however, the total of the number when split is short a few
> decimal places. I think this may have something to do with rounding
> up.
>
> Can anyone make anysence of this??
>
> =IF((($AF41*FL$4)-($AF41*FL$4))>0.2499,CEILING(($AF41*FL$4),0.5),IF(($AF41*FL$4)>0,($AF41*FL$4),0))
>
> Thanks,
>
> Sam
>
>
> --
> scriblesvurt
> ------------------------------------------------------------------------
> scriblesvurt's Profile: http://www.excelforum.com/member.php...o&userid=24399
> View this thread: http://www.excelforum.com/showthread...hreadid=478169
>
>