Excel Formula help.

[dnraod]

Hi,

I wanted to get how many numbers in the list of range numbers are equal to 305.

For ex,there are range of values like 100-110,320-350,405-415,430-445 like this there are around 100 ranges i want to know which formula can give all the range details.

 

[Hui]

Dnraod


Firstly welcome to the Chandoo.org Forums


Do you mean the number 305 is in the range ?

eg: 305 is not in the range 100-110 or 405-415


or


the Whole Range sums to 305 ?

in which case any number > 305 cannot sum to 305


or

parts of the range sum to 305 ?


eg 100, 102 and 103 sum to 305


Please explain a bit more

 

[dnraod]

Hi Hui,


Thanks for your instant reply.


There are n no of ranges not only 100-110,405-415 there are other ranges like 300-310,305-325 etc.


It is not sum or range sum .I am talking about the range where 305 comes in between 300-310,305-325.I want those ranges to be highlighted.

 

[Hui]

If 300 and 325 are in separate cells A1 and A2 try:

=IF(AND(310>=A2,310<=B2),TRUE, FALSE)


If you have 300-325 in Cell A3 try

=IF(AND(310>=VALUE(LEFT(A3,3)),310<=VALUE(RIGHT(A3,3))),TRUE,FALSE)

 

[dnraod]

Hi Hui,


Thanks for the reply it is working with the ranges as you said.

I have another query can this IF formula work with Vlookup formula since i have that requirement also of bringing the column data.if it is possible can you give that example .

 

[Hui]

Do you want to lookup the 310 or the other values?

Can you post more details of what your trying to achieve