error with formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

prem - 03 May 2008 03:48 GMT

Hi guys here is the issue.

Under column A, which is titled serial number, I have listed the formula
=IF(ISNUMBER(B28)=TRUE,A27+1,""). What this does is that it detects if there
is a date in column B and then inputs a serial number that is one more than
the previous one. If not the cell in column A is just left blank if no date
is detected.

Under column D, entitled Receipt number, I have inputed the formula
=IF(MOD(A27,8)=0,"","NA"). What this does is it detects the serial number in
column A and checks if it is a muliple of 8. If it is the cell in column D is
left blank. If not "NA" is displayed. However if there is no serial number at
all, I get a #VALUE! symbol.

To get rid of the symbol, I tried a new formula in column D,
=IF(ISNUMBER(A28),IF(MOD(A28,8)=0,"","NA"),"")
What I am trying to achieve is that if no number is detected in column A,
then column D should remain blank. If there is a number detected, then
=IF(MOD(A28,8)=0,"","NA") should be applied. However, this does not seem to
work. What am I doing wrong?

Reply to this Message

T. Valko - 03 May 2008 04:34 GMT

>=IF(ISNUMBER(A28),IF(MOD(A28,8)=0,"","NA"),"")

Your formula is correct. What's not working?

Signature

Biff
Microsoft Excel MVP

> Hi guys here is the issue.
>
[quoted text clipped - 23 lines]
> to
> work. What am I doing wrong?

Reply to this Message

prem - 03 May 2008 05:01 GMT

Ah yes it is working. I actually inputed the worng cell number. So sorry
about this.

Anyway thank you for your input Max. And thank you Biff for confirming that
my formula is actually correct. Appreciate it. Could you also check my post
"grabbing data from 1 sheet to place in another" to see if you could help?
Its about 16 posts down from this one. Would really appreciate this.

Again thank you guys.

Prem

> >=IF(ISNUMBER(A28),IF(MOD(A28,8)=0,"","NA"),"")
>
[quoted text clipped - 27 lines]
> > to
> > work. What am I doing wrong?

Reply to this Message

Max - 03 May 2008 13:10 GMT

One crack given in that thread, with a link to a working sample to illustrate.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Max - 03 May 2008 04:35 GMT

Try these, which basically checks A27 for blanks / zero length null strings:
""

In C28:
=IF(ISNUMBER(B28),IF(A27<>"",A27+1,""))

In D28:
=IF(A27="","",IF(MOD(A27,8)=0,"","NA"))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi guys here is the issue.
>
[quoted text clipped - 16 lines]
> =IF(MOD(A28,8)=0,"","NA") should be applied. However, this does not seem to
> work. What am I doing wrong?

Reply to this Message