Formula - Do not add zero's

ChrisMac1

New Member
Joined
Jul 15, 2024
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Bit hard to be sure without some sample data and expected results, but try this
Excel Formula:
=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?
 
Upvote 0
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.
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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. ;)
 
Upvote 0
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. ;)
 
Upvote 0

Forum statistics

Threads
1,224,816
Messages
6,181,141
Members
453,021
Latest member
Justyna P

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
Back
Top