Hi,

I have written two different types of nested if statement. One which has more arguments/conditions returns an empty cell which is what it should if none of the conditions are satisfied

=IF(AND(E$3= "OU", E$10="Without"),Parts!$C16,IF(AND(E$3 = "SS", E$10 ="without"), Parts!$D16,""))

The one I am having a problem with is returning a dash instead of an empty cell. I can't work out where I am going wrong.

=IF(E$3= "OU", Parts!$C15,IF(E$3 = "SS", Parts!$D15,"")). I am picking information up on another sheet for with another formula, from this sheet which should return nil values for the blank cells, however this is not happeneing where there is a dash. It is reading that there is a value there.Can anyone please help

I'm confused. I'm having trouble deciphering the grammer in this statment: re: "I am picking information up on another sheet for with another formula, from this sheet which should return nil values for the blank cells, however this is not happeneing where there is a dash."

Maybe if you provided an example of the data you are looking at, we can help.

Thanks DerbyDad. The formula on the other page is

=IF('Individual parts list'!C17="","",'Standard Costs'!F16)Basically the original formulas I posted come from the "Individual Parts List" page and this formula comes from the "Standard costs" page. We make two bespoke items which would be either an OU or SS but then they may have other specifications which I have produced the more indepth "IF" formulas which include the "AND" formula (eg the first of the two formulas in my original post). So the list of parts in column C on the "Parts" sheet is for OU and column D is for "SS" - the contents of these cells are an "a" in Marlett which shows as a tick. The "individual parts list" page basically just gives a list of parts specific to each project. The "Standard costs" page transfers this list into values and also pulls in the values associated to external work and labour giving a definitive cost to each project. Individual Parts list should also be useable as a picking list for stores.

I hope this is of help. Please ask further if not.

Thanks

That's a wonderful explanation of what you are doing with your worksheets. Maybe it's just me, but unfortunately, it doesn't help me understand the issue related to the "dash".

I'll repeat what I said in my earlier response:

Maybe if you provided an example of the data you are looking at, we can help.

Show us some examples of the data the IF statements refer to (the inputs) and show us the desired results (the output).

I have just been playing about with it and think I have found the problem but I still don't know how to fix it. I used the evaluate formulas - bear with me I'm still learning Excel 2007 and didn't even know that button existed.

The last part of the formula which asks it to return "" an empty cell is actually returning a 0 which is a dash in Marlett instead of an empty cell. The formulas with "AND"s is returning an empty cell

Not sure exactly what you mean by the inputs and outputs.

OK - I think I have got it, but still need to know how to fix it. If the if formula does not satisfy any of the criteria it is doing as it is told and returning an empty cell.

=IF(E$3= "OU", Parts!$C15,IF(E$3 = "SS", Parts!$D15,""))

With this formula it either picks up the information in cell Parts!$C15 or Parts!$D15, so if it is an empty cell it is returning a 0 - hence the dash as its formatted to Marlett.How can I get it to show a blank cell? I have tried to copy and paste extracts from the file but that doesn't really work very well. Can you suggest anything for sending more details?

Alternatively if you can simulate - I have two columns with ticks in showing the parts to make an OU and the parts to make an SS. Some parts are the same in each, some are unique to one or the other product. If you made up two columns in an excel spreadsheet with random ticks in either column maybe?

Thanks for your help

Thanks for your help but I have just tried something which has worked. I put an "AND" argument in the IF that wasn't working asking it to only put the value in if there was one there, else force it to return an empty cell

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History