"if" formula not working- seems to be formatting

  • Thread starter Thread starter GaiGauci
  • Start date Start date
G

GaiGauci

Hi All
I have a formula in Col B =if(G2>"99999","StaffType1","StaffType2"),"")
Col G has the staff employee numbers in it which if they are a 5 digit
number they show a StaffType2 officer and if a 7 digit number show
StaffType1. I use this with the download from SAP which is cut and pasted
into D2 to O2 onwards.

My formula was working. But if I keep trying it (and I can't seem to
deliberately replicate), sometimes it shows everyone as StaffType1.

I am thinking a format problem in column G as if I just click in each G cell
and enter, it seems to correct itself. But I can't do this for all the cells
or I'll go mad!! I have tried General, text and number formats because I am
running out of ideas.

Cheers
Gai
 
=if(G2>"99999","StaffType1","StaffType2"),"")

Maybe this...

=IF(B2="","",IF(--G2>99999,"StaffType1","StaffType2"))
 
--Enter 0 in a cell. Copy the cell
--Keeping the copy Select column G.
--Right click>PasteSpecial>Add. Click OK.

Now try

=if(G2>99999,"StaffType1","StaffType2")


If this post helps click Yes
 
Just paste this formula in B2 cell

=IF(--TRIM(LEN(G2))=5,"STAFF TYPE2",IF(--TRIM(LEN(G2))=7,"STAFF TYPE1",""))

Now copy the B2 cell formula and apply it for the remaining cells.

Change the cell reference G2 to your desired cell if required.

If this post helps, Click Yes!
 
Back
Top