Count number of cells based on 2 selections in the same range.

Count a cell to another cell based on two selections.

B1 & B2 are 2 dropdown boxes taking their inputs from a same range. When both are selected, how to count between them.

example worksheet is attached.

If B1 selection is Master (27) & B2 is Stars (3) : the difference is 4.

In my book that is 24 ??

8-(4*7)=20/9=2 & 1 Remainder

Again 20/9 = 2 & 2 (18 & 2) remainder ??

You are right. The remainder 2 should match the good, fair list (result range) and B3 should show BAD as result.

thanks.

... and the diff between B1 & B2.

here is something straight forward.

Result: =VLOOKUP(MOD(8-SQRT((VLOOKUP($B1,$M:$N,2,FALSE)-VLOOKUP($B2,$M:$N,2,FALSE))^2)*7,9),$J$3:$K$10,2,FALSE)

Hi, Your worked example doesn't look correct.

First of all if B1 = Master - value 27, and B2 = stars - value 3, the difference is 24 and not 4 as you say.

Secondly 20 divided by 9 = 18 remainder 2 and not 1 as you say.

Thirdly you mention two constants, 8 & 7. What about the 9 value, is that a constant too?

Would you clarify?

Rgds

Buttrey :

Thanks.
I mentioned it. It is mere counting from cell to cell. "Count number of cells based on 2 selections in the same range."
Remainder is 2 only. I am very weak in calculations and xl
9 is also a constant.

I changed the query.xls example attachement.

Originally Posted by rwgrietveld

... and the diff between B1 & B2.

here is something straight forward.

Result: =VLOOKUP(MOD(8-SQRT((VLOOKUP($B1,$M:$N,2,FALSE)-VLOOKUP($B2,$M:$N,2,FALSE))^2)*7,9),$J$3:$K$10,2,FALSE)

Will try this and let u know. Thanks.

The sample file itself having some mistakes. I corrected the mistakes and uploaded again. If someone can solve it, will be thankful.

Hi,

I still can't reconcile your calculation. master is indeed as you say the 27th row, but Stars is the 3rd row. Isn't that a difference of 24 and not 4?

Assuming the above is correct then one way is:

Please Login or Register  to view this content.

HTH

Buttrey, thanks again.

Master is in 27th row. BUT, it is the count starting point "1". After 27th, First row, Second row, Third row. So total 4 rows. 4 is the answer for that part, not 24. Hope its clear now.

Since what you assumed is contrary to what i meant, your solution is not working.

Hi,

It wasn't clear that this was a sort of circular referencing system. One way is

Please Login or Register  to view this content.

HTH

RButtrey: Thanks.

Yes i missed to mention its circular ref. I will try your solution and come back.

RButtrey : Thanks.

Still something is wrong. Better, i will try little more and rewrite my example file, then start a new thread.