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

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