Hi All

I just working through a formula to breakdown salary i.e 50's 20's 10's ...

First 50's Formula =IF($B2="","",INT($B2/C$1))

Second 20's Formula
=IF($B2="","",INT(($B2-SUMPRODUCT(($C2:C2)*($C$1:C$1)))/D$1))

But this formula does not work with some instances. Please check the
attached excel worksheet for example.

Regards
Bhavesh

There's no attached spreadsheet in discussion groups. Explain what doesn't
work, what errors you're getting, etc.

Dave
--
Brevity is the soul of wit.


"Bhavesh Valand" wrote:

Hi All

I just working through a formula to breakdown salary i.e 50's 20's 10's ...

First 50's Formula =IF($B2="","",INT($B2/C$1))

Second 20's Formula
=IF($B2="","",INT(($B2-SUMPRODUCT(($C2:C2)*($C$1:C$1)))/D$1))

But this formula does not work with some instances. Please check the
attached excel worksheet for example.

Regards
Bhavesh

=IF($B2="","",INT($B2-(C2*C1)/D$1))

"Bhavesh Valand" wrote:

Hi All

I just working through a formula to breakdown salary i.e 50's 20's 10's ...

First 50's Formula =IF($B2="","",INT($B2/C$1))

Second 20's Formula
=IF($B2="","",INT(($B2-SUMPRODUCT(($C2:C2)*($C$1:C$1)))/D$1))

But this formula does not work with some instances. Please check the
attached excel worksheet for example.

Regards
Bhavesh

Bhavesh,

Checking out your attached spreadsheet, you are expecting 2 in cell M12.
Using the INT funcion rounds the number *DOWN* to the nearest integer, it
does not round it normally. If M11 was = 2 and < 3, M12 would be 2. But
M11's value is 1.9999999999996 (increased the decimal places to see the
actual value), so it is rounding it down to 1 (the number displayed is the
actual value rounded to the number of decimal places the format is set to).

For some reason, SUMPRODUCT is not calculating an exact amount (to 2 decimal
places--it is going 13 places). I wrapped a ROUND function around your
existing formula and it looks like to worked okay. I'm going to attach your
original attacment with my modifications to it (even though some one said
that we are not supposed to include attachments).

Hope this helps.

Conan



"Bhavesh Valand" wrote in message
...
Hi All

I just working through a formula to breakdown salary i.e 50's 20's 10's
...

First 50's Formula =IF($B2="","",INT($B2/C$1))

Second 20's Formula
=IF($B2="","",INT(($B2-SUMPRODUCT(($C2:C2)*($C$1:C$1)))/D$1))

But this formula does not work with some instances. Please check the
attached excel worksheet for example.

Regards
Bhavesh