Formula - Do not add zero's

[ChrisMac1]

Hi there, I'm hoping this is an easy fix.

I have a formula that returns 0's when there are blank cells in the results, what do I need to add and where so zero's are not added?

=IFERROR(IF(VLOOKUP(D2,'ARCHIVE SENT'!$D$2:$AK$10000,32,)>0,VLOOKUP(D2,'ARCHIVE SENT'!$D$2:$AK$10000,32,),AI2),AI2)

Many thanks,
Chris

 

[Peter_SSs]

Bit hard to be sure without some sample data and expected results, but try this

=LET(v,IFNA(VLOOKUP(D2,'ARCHIVE SENT'!$D$2:$AK$10000,32,0),0),IF(v>0,v,IF(AI2="","",AI2)))

BTW,

• what sort of values (ie text or numerical) are in column AI of the formula sheet?
• what sort of values (ie text or numerical) are in column AI of the ARCHIVE SENT sheet?

 

[ChrisMac1]

Thanks very much Peter, however, as the formula was not written by me and is part of a shared doc that others are updating, I would prefer to only tweak this one...

=IFERROR(IF(VLOOKUP(D2,'ARCHIVE SENT'!$D$2:$AK$10000,32,)>0,VLOOKUP(D2,'ARCHIVE SENT'!$D$2:$AK$10000,32,),AI2),AI2)

Is that possible?

The results are a mixture of text and numbers.

 

[Peter_SSs]

The results are a mixture of text and numbers.

Does that mean some cells are text and some cells are numbers or does it mean all the cells have both text and digits in them?
Also, what doe "results" mean? Does that mean column AI in each of the worksheets?

as the formula was not written by me and is part of a shared doc that others are updating, I would prefer to only tweak this one...

=IFERROR(IF(VLOOKUP(D2,'ARCHIVE SENT'!$D$2:$AK$10000,32,)>0,VLOOKUP(D2,'ARCHIVE SENT'!$D$2:$AK$10000,32,),AI2),AI2)

Is that possible?

It should be possible but
a) Did you try my suggestion and if so did it work? (That might give a clue as to how to, or how not to, tweak this formula)
b) If a more efficient formula does exist, wouldn't it be a good idea to suggest it to the others (provided they are using an Excel version that has the relevant functions)?

 

[ChrisMac1]

No, sorry, I have not tried your code, but I do appreciate the effort.

As I say, I would like to simply update the code I have if possible.

 

[Peter_SSs]

As I say, I would like to simply update the code I have if possible.

Yep, and if I knew whether or not the other formula produced the results you want it would help me know how to update it.
It isn't very hard to try is it?


.. and what about my first couple of questions above?

Does that mean some cells are text and some cells are numbers or does it mean all the cells have both text and digits in them?
Also, what doe "results" mean? Does that mean column AI in each of the worksheets?

 

[ChrisMac1]

Pasted your code into one column and it worked, yes, no zero's, but for some reason the text changed to Inherit?

Let's please keep the chat friendly. "It isn't very hard to try is it?" was not received very well.

 

[Peter_SSs]

Pasted your code into one column and it worked, yes, no zero's, but for some reason the text changed to Inherit?

That sounds like it didn't actually work how you want then?
If that is the case then I think that I would need a few rows of varying dummy sample data, the expected results and an explanation of those expected results, or at least a couple of them.
You are obviously very familiar with your data and what you are trying to do but we have no idea other than what you show and tell us. So far, all we really have is a formula that doesn't do what you want so we are pretty much in the dark.
Could you use XL2BB to provide say 5 or 6 rows of sample data from the sheet that the formula is to go on? Before you use XL2BB on that sheet, hide all the columns between column D and column AI and manually enter the expected results in, say, column C.
Then could you post some relevant corresponding data from the archive sheet, again hiding columns between D and AI?

Let's please keep the chat friendly. "It isn't very hard to try is it?" was not received very well.

That's understandable, but it is also not so well received when you put a fair bit of time into trying to help somebody and they don't even try a suggestion you make and ignore your requests for further information.

 

[ChrisMac1]

That sounds like it didn't actually work how you want then?
If that is the case then I think that I would need a few rows of varying dummy sample data, the expected results and an explanation of those expected results, or at least a couple of them.
You are obviously very familiar with your data and what you are trying to do but we have no idea other than what you show and tell us. So far, all we really have is a formula that doesn't do what you want so we are pretty much in the dark.
Could you use XL2BB to provide say 5 or 6 rows of sample data from the sheet that the formula is to go on? Before you use XL2BB on that sheet, hide all the columns between column D and column AI and manually enter the expected results in, say, column C.
Then could you post some relevant corresponding data from the archive sheet, again hiding columns between D and AI?



That's understandable, but it is also not so well received when you put a fair bit of time into trying to help somebody and they don't even try a suggestion you make and ignore your requests for further information.

 

[ChrisMac1]

Appreciate your time Peter.