AND OR Functions

[GordonP]

Hi All,

I have a situation that i need to create a formula to cater for the following:

I have a range of 7 cells and i want to create a formula to tell me if all the range cell are filled and or if all the range cells are empty it will return "1".

While if the range of cells is not all completely filled it returns with ïncomplete""

I managed to create separate formulas to show if all cells are filled and another one if all empty but i cant figure out how to amalgamate them together.

[mehmetcik]

Please Login or Register  to view this content.

[AlKey]

A shorter version

Please Login or Register  to view this content.

[GordonP]

Thanks for your prompt rely. It is not working as i wish too.

The first column contains a letter while the rest contain a number. And Therefore when i enter just a character in the first column it is reading as if the range is all full.

Secondly, The last 3 columns corresponds to meter, feet and inches. So I need this formula to calculate these 3 columns as one. That is it is either the meter column filled or the feet and/or the inches. The feet and inches can be filled in the same line but you cannot had also the meter column filled.

Thanks in advance

[mehmetcik]

Please Login or Register  to view this content.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[GordonP]

sample attached.

Row 14,15,27 and 28 are the right combination.

Now I need to get "1"in column H if the whole range of cells (A:F) are either empty or filled in ONLY as row 14,15,27 and 28. So when I get 1 in column H the rest of the sheet can work out the other formulas.

If not Column H should still read as Incomplete.

Rows 5-16 are one formula and rows 18-29 is the shorter version that was also suggested.

[hemesh]

may be in H5 try below

Please Login or Register  to view this content.

if you need you can change incomplete to 0

[mehmetcik]

This works

Please Login or Register  to view this content.

This Also works.

Please Login or Register  to view this content.

[GordonP]

Thanks we are getting there.

Hopefully the last issue is that if you need to enter length only in feet like row 10 and only in inches like row 11? but you need to enter in column a,b and c just like those in row 14 and 15.

Also when you have all the range empty i would like to still get a "1"like row 5

[GordonP]

This refers only to the full range of empty cells? Right? Is it possible to have it combined with the previous formula?

Also what about to input only feet or inches? Also I would like it combined with the previous formula.

[newdoverman]

Would this work for you?

Please Login or Register  to view this content.

Shorter version

Please Login or Register  to view this content.