Only IF all cells between a range has a certain value.

[GSAN]

Hi just wondering if anyone could help.

this is my formula i need help with... I'm pretty new at this so any help would be appreciated.

=IF(COUNTIF(G10:M10,"I")>=1,"I",IF(COUNTIF(G10:M10,"L")>=1,"L",IF(COUNTIF(G10:M10,"U")>=1,"U",IF(COUNTIF(G10:M10,"0")=7,"0",IF(COUNTA(G10:M10,""),"",

this is the particular line I'm having trouble with

IF(COUNTIF(G10:M10,"0")=7,"0"

what I'm trying to do is; if all the cells between G10 & M10 equal 0 then that cell will equal 0..

i hope that makes sense!!

thanks in advance

[TMS]

"0" is a text string and is not the same as numeric 0 (zero)

Regards, TMS

[GSAN]

the 0 is the value I want to be shown. the only way I could do was have the =7 i.e. 7 cells from G to M...

the cell will change to an ILU0 depending on the value of the 7 cells... but i wanted to have it between a certain range encase someone added or removed a cell.

does that make sense??

[Punnam]

A sample workbook without any confidential data showing u r desire results will be helpful to understand u r logic .

Punnam

[GSAN]

Sample - Copy.xlsm

please see the blue coloured cells where the formula is...


so what i would like is if the end user inserts another column it will get counted between the range.

[cbatrody]

What if "I", "L" & "U" all >=1 in that range?

e.g. in row 8: I & L both are >=1

[GSAN]

the values go I to L to U
The over all level would be equal to the lowest value so I

only if each level has been entered as a 0 will the Over all level be a 0

[cbatrody]

In row 8: 1"I" & 2"L"
In row 9: 4 "I" & 3"U"

what is the expected outcome here? and why?


As per your current formula, when there is an "I" in the range, the outcome will always be "I" no matter how many "U" & "L"s are there in the range.

[sktneer]

May be this.....

Please Login or Register  to view this content.

[GSAN]

the expected outcome for the overall would be the lowest level which is the I.

the ILU0 is just represents a different level of understanding...

"I" being a learner and "0" being a wizard at it.

[GSAN]

May be this.....

Please Login or Register  to view this content.

thanks but the only problem with that is if you delete a 0 the overall level is still at 0 where it would need to be blank if its not all filled as 0

[sktneer]

Try this......

Please Login or Register  to view this content.

[GSAN]

Try this......

Please Login or Register  to view this content.

this seems to work! thanks a lot much appreciated!!!

[boopathiraja]

If I understand correctly this woud be the formula you expected

Please Login or Register  to view this content.

[sktneer]

You're welcome.
If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
You may also click on * (star) to Add Reputation if the solution provided helped you. This is another way to say thanks to them.