Formula in Office 2013

klatlap

Well-known Member
Joined
Sep 1, 2004
Messages
597
Can someone please explain why a simple formula that has always worked now doesn't always work in Office2013

=if(A1="","",1

Yes there is a formula in A1 but that has always worked except now
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Can you define "doesn't always work" ??

What do you mean 'doesn't always' ?
To me that says sometimes it DOES work.
So in which cases DOES it work, and which cases does it NOT work?

And what does Not Work mean?
Do you get an error? What error?
Do you get the wrong result? What result DO you get? What result did you expect?

What do you actually expect the formula to do ?
The formula you posted isn't even complete, though I'm sure that's just a result of you manually typing it into the thread, instead of just copying it from Excel to the Thread.
 

klatlap

Well-known Member
Joined
Sep 1, 2004
Messages
597
no error, here are 2 formulas.

=IF(AM3="","",AM3) works fine

=IF(AM3="","",IF(AO4="",(($Q$3*$Q$7-$Q$3)+$Q$5)*0.92-(($Q$3*$Q$7-$Q$3)+$Q$5),IF(AX4=0,AJ2*T4,AW4*T4))) This should result in a blank cell like the first formula, but instead it continues to process the calculation.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
The logical conclusion is that the first formula is not doing exactly what you think it is.
AM3 is probably not really blank, but it just looks blank.
So the AM3="" condition is actually FALSE, and the formula is returning the contents of AM3.
But the contents of AM3 'appears' to be blank, but it's really not.
Maybe it contains a space, or a 'hard return' or some other strange character.

What do these return
=AM3=""
=LEN(AM3)
 

klatlap

Well-known Member
Joined
Sep 1, 2004
Messages
597

ADVERTISEMENT

False and 1 so i guess you are correct, it is a Vlookup, i will try to add Trim(Clean( to my formula and associated formulas, not sure what else would work.
 

klatlap

Well-known Member
Joined
Sep 1, 2004
Messages
597
=IF(ISNA(TRIM(CLEAN(VLOOKUP($AB$4,Results!$T$2:$W$200,2,0))))," ",TRIM(CLEAN(VLOOKUP($AB$4,Results!$T$2:$W$200,2,0))))

I added the trim clean, didn't help so i did another work around to solve this, but would be great to solve the original issue for future projects.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
=IF(AM3="","",AM3)

=IF(ISNA(TRIM(CLEAN(VLOOKUP($AB$4,Results!$T$2:$W$200,2,0))))," ",TRIM(CLEAN(VLOOKUP($AB$4,Results!$T$2:$W$200,2,0))))

" " is not the same thing as ""
" " is actually a SPACE character.

So your vlookup, or actually the IF(ISNA), is putting a SPACE in the cell, not an empty string.
So if the vlookup is an error, then AM3 = " "
therfore
AM3="" is indeed FALSE.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,396
Members
414,235
Latest member
sucosama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top