Round/Ceiling on an IF function returning numerical value or text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

donnaK - 13 Dec 2005 15:36 GMT

Excel 2000.

This formula returns an error of #VALUE when I add the rounding, instead of
the text "No Fica Due" Does anyone have any suggestions? I believe I must
have the cell formatted as "number" for when the cell returns a numerical
value.

=CEILING(IF(IF(J25<90000,0.062*I25,(90000-J24)*0.062)<0,"No FICA
due",IF(J25<90000,0.062*I25,(90000-J24)*0.062)),0.002)

Reply to this Message

Bernie Deitrick - 13 Dec 2005 16:03 GMT

Donna,

See if this works for all cases....

=IF(CEILING(MAX(IF(J25<90000,0.062*I25,(90000-J24)*0.062),0),0.002)<=0,"No FICA
Due",CEILING(IF(J25<90000,0.062*I25,(90000-J24)*0.062),0.002))

HTH,
Bernie
MS Excel MVP

> Excel 2000.
>
[quoted text clipped - 5 lines]
> =CEILING(IF(IF(J25<90000,0.062*I25,(90000-J24)*0.062)<0,"No FICA
> due",IF(J25<90000,0.062*I25,(90000-J24)*0.062)),0.002)

Reply to this Message

Peo Sjoblom - 13 Dec 2005 16:10 GMT

You need to use CEILING within the IF formula (multiple times I guess)
or else you'll get the error, maybe you can use

=IF(J25<90000,CEILING(0.062*I25,0.002),IF((90000-J24)*0.062<0,"No FICA
due",CEILING((90000-J24)*0.062,0.002)))

Signature

Regards,

Peo Sjoblom

> Excel 2000.
>
[quoted text clipped - 5 lines]
> =CEILING(IF(IF(J25<90000,0.062*I25,(90000-J24)*0.062)<0,"No FICA
> due",IF(J25<90000,0.062*I25,(90000-J24)*0.062)),0.002)

Reply to this Message

donnaK - 13 Dec 2005 16:20 GMT

Yes Peo, that works. Thanks alot

> You need to use CEILING within the IF formula (multiple times I guess)
> or else you'll get the error, maybe you can use
[quoted text clipped - 13 lines]
> > =CEILING(IF(IF(J25<90000,0.062*I25,(90000-J24)*0.062)<0,"No FICA
> > due",IF(J25<90000,0.062*I25,(90000-J24)*0.062)),0.002)

Reply to this Message

B. R.Ramachandran - 13 Dec 2005 16:16 GMT

Hi,

Embed the CEILNG function at each calculation point in your formula. When
'No Fica due" is to be answer, your formula is trying to calculate
=CEILING("No Fica due", 0.002) and returns an error message. Try the
following formula:

=IF(IF(J25<90000,CEILING(0.062*I25,0.002),(90000-J24)*0.062)<0,"No FICA
due",IF(J25<90000,CEILING(0.062*I25,0.002),CEILING((90000-J24)*0.062,0.002)))

Regards,
B. R. Ramachandran

> Excel 2000.
>
[quoted text clipped - 5 lines]
> =CEILING(IF(IF(J25<90000,0.062*I25,(90000-J24)*0.062)<0,"No FICA
> due",IF(J25<90000,0.062*I25,(90000-J24)*0.062)),0.002)

Reply to this Message