URGENT - Multiple IF formulas for a single cell!

Hello all!

Example Sheet for Excel Formula.xlsx

I am currently working on a worksheet where I have got quantities of a chemical in 3 different dilutions 1:1, 1:10 and 1:40. Now I need a 4th column next to each one of these which will analyse the values in the first three columns and display the one that is of most use.
I need the formula to prioritise based on:
1 - Numerical values over blank cells or those displaying "ND"
2 - If there are more than one numerical value the cell should prioritise displaying 1:40 before 1:10 before 1:1

I have attached a mock up of the data I am working on with columns A-C displaying the data for the dilutions and column D showing the correct value from thouse available in A-C. I have filled in the correct value to show what I am trying to achieve, but the data I am working on is significantly larger, (33 groups of 4 columns and 500+ rows), hence the need for a formula to do it.

I had a look at this web page http://www.techonthenet.com/excel/fo.../if_nested.php which seems to be along the right lines, but I cant seem to make it work for my data.

Any help would be appreciated,


M

copy and paste this formula

Please Login or Register  to view this content.

Originally Posted by easyexcelanswers

I have easier solution
=IF(MAX(A2:C2)>0,MAX(A2:C2)," ND")

The problem with this is it seems to populate the cell with the largest answer, which isn't what I want. For example, when pasted into the spreadsheet that I attached in cell D6 it shows 1.27, when it should show 1.20.
Any ideas how to work around that?

Did you try my formula?

My formula doesn't look for the Max value, since on the 2nd condition, if there's more than 2 numerical formula, it should prioritize 1:40 before 1:10 before 1:1

Originally Posted by dluhut

copy and paste this formula

Please Login or Register  to view this content.

This seems to work perfectly, going to apply it to the real spreadsheet and make sure. Thank you so much for the help!!

Originally Posted by M.Knight

This seems to work perfectly, going to apply it to the real spreadsheet and make sure. Thank you so much for the help!!

If it does work, remember to mark this thread as solved =)

Ok, it works in one respect, but it is displaying a blank cell if the 1:40 cell (Column C) has a blank cell, even if B or A have a numerical value in the cell. Any ideas how to fix that?

Originally Posted by M.Knight

Ok, it works in one respect, but it is displaying a blank cell if the 1:40 cell (Column C) has a blank cell, even if B or A have a numerical value in the cell. Any ideas how to fix that?

I tried to look check with inputting a new row where column A and B has values and column C is blank, and I still got the result from column B.

Check if your column C is indeed blank. Because, if column C has a space, to our eye, although it's blank, in fact it's not.

The fastest way to delete those "spaces" that seems to be blank in our eyes, filter each column, choose '(blank)' and then highlight those "blanks" and on your keyboard press 'Delete'

Originally Posted by dluhut

I tried to look check with inputting a new row where column A and B has values and column C is blank, and I still got the result from column B.

Check if your column C is indeed blank. Because, if column C has a space, to our eye, although it's blank, in fact it's not.

The fastest way to delete those "spaces" that seems to be blank in our eyes, filter each column, choose '(blank)' and then highlight those "blanks" and on your keyboard press 'Delete'

Yep that was it!! The ND had a space after it and the black cells also has a space in them. Solved by replacing all the "ND " cells with "ND".

Solved, thank you so much for your help, random internet genius!

Originally Posted by M.Knight

Yep that was it!! The ND had a space after it and the black cells also has a space in them. Solved by replacing all the "ND " cells with "ND".

Solved, thank you so much for your help, random internet genius!

Glad that I could be of help. Remember to mark this thread as solved =)

Originally Posted by dluhut

Glad that I could be of help. Remember to mark this thread as solved =)

Ok, not quite solved.

The issue I am encountering now is that if A and C are blank and B is "ND" the cell shows "Incomplete Formula".

In any situation with no numerical value in any of the 3 cells I need cell D to show ND, UNLESS there are no values in any cell (all empty) in which case it can say Incomplete Formula.

Sorry to bother again.

Originally Posted by M.Knight

Ok, not quite solved.

The issue I am encountering now is that if A and C are blank and B is "ND" the cell shows "Incomplete Formula".

In any situation with no numerical value in any of the 3 cells I need cell D to show ND, UNLESS there are no values in any cell (all empty) in which case it can say Incomplete Formula.

Sorry to bother again.

I've modified the formula a little.

Please Login or Register  to view this content.