• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Result 0 when data validation selection is blank or zero

T

Thomas Kuriakose

Active Member
Respected Sirs,

Kindly find attached the sheet where I need the value to be zero or blank if the data validation selection is blank (Cell D8 to M8) or not in the selection.

Currently when blank is selected the value is #n/a.(Cell D15 to M15).

I tried many times but could not get the desired result.

Thank you so much,

with regards,
thomas
 

Attachments

  • Cost Sheet.xlsx
    43.6 KB · Views: 2
Check your formulas. They have #REF! error. Seems a sheet has been deleted so reference is invalid. Might be the reason
 
Respected Sir,

There are only two sheets.

Kindly recheck this attachment.

Thank you so much,

with regards,
thomas
 

Attachments

  • Cost Sheet.xlsx
    43.4 KB · Views: 8
Your #N/A is being propagated by trying to lookup a value that does not exist in the Lookup in Cells D8:M8. Wrap the formula in an IFERROR("Your formula",0) and it should fix it for you, If I've correctly understood your problem ...

Throwing this into D15 produces the desired result ...

=IFERROR(IF(SUM(D10:D11)>0,IF(D10>0,VLOOKUP(D8,'Data Validation & Input Sheet'!$A$3,2,0)*D10)+IF(D11>0,VLOOKUP(D8,'Data Validation & Input Sheet'!$A$3:$C$7,3,0)*D11),0),0)
 
Respected Sirs,

Thank you for this information. I have used IFERROR, but

now I get FALSE for some values. It should be blank or zero.

Kindly check this.

Thank you so much,

with regards,
thomas
 

Attachments

  • Cost Sheet.xlsx
    42.1 KB · Views: 3
Your formula in D15:M15 is incomplete -

try putting the following in D15 and copy it across ...
=IFERROR(IF(SUM(D10:D11)>0,IF(D10>0,VLOOKUP(D8,'Data Validation & Input Sheet'!$A$3,2,0)*D10)+IF(D11>0,VLOOKUP(D8,'Data Validation & Input Sheet'!$A$3:$C$7,3,0)*D11),0),0)
 
Respected Sirs,

I need one more help, with the formulas in row D16 & D17, I tried using sum product but could not get the result.

Kindly assist with a better formula for D16 and d17.

Thank you so much,

with regards,
thomas
 
Thomas Kuriakose said:
Respected Sirs,

I need one more help, with the formulas in row D16 & D17, I tried using sum product but could not get the result.

Kindly assist with a better formula for D16 and d17.

Thank you so much,

with regards,
thomas
Click to expand...

Thomas - Insofar as Row 16 is concerned, If I'm understanding your worksheet correctly, I think you are over-complicating your formula by testing if the values in D10 and D11 are 0 - just go ahead and lookup the value and multiply it by the values in rows 10 and 11 ...
D16 formula
=(VLOOKUP($C$6,'Data Validation & Input Sheet'!$D$3:$F$11,2)*D10)+(VLOOKUP($C$6,'Data Validation & Input Sheet'!$D$3:$F$11,3)*D11)

As you're restricting the values in $C$6 to your list values, you should not have any issues with #N/A

I think you can also simplify your lookup tables into a single table ... but I'm willing to be convinced otherwise.

Insofar as Row 17 is concerned, my copy of the file has a very long reference to a file location on your drive, which I suspect looks exactly like the Data Validation & Input Sheet in this workbook - the clue is the Sheet Name in your other file is DVIS ...

I'm pretty sure you can modify the formula for Row 16 to satisfy your needs in Row 17 - the logic is virtually the same - you're just adding the result of several lookups.

Generally, I think you can simplify your worksheet dramatically - as in most things in life, simplify, simplify, simplify is a good mantra!
 
You must log in or register to reply here.
Back
Top