Excel help please!

afrobeater

Hi, I would be very grateful for some help on this. In Excel, let's say I enter a value in cell A1, and I want Excel to take that value, run some comparison criteria, and return a Boolean result to cell B1.

Sounds straightforward enough. So let's say the criteria is a range, say, 2 to 4, so if A1's value is >= 2, or is <=4, then we return a "1" to B1, otherwise, we return a "0".

So I figure one way of doing this is to put the following formula in B1:

=IF(AND(A1>=2,A1<=4),"1","0")

My question is, if I want to update that range (2 to 4) I need to step into the formula and edit it. Is there a way to have Excel automatically work out the range from a cell that simply has 2-4 (as text) written in it?

So I would have 3 cells, the input cell (A1), the criteria cell (B1) and the output cell (C1). C1 would contain the code, and the criteria cell B1 would simply contain: 2-4. If I then was to change B1 to 2-8, the code in C1 would automatically update based on the values I enter in B1.

I want to do this for a large number of cells, so I want to make the formula able to 'work out' the range of values from the text in the cells, e.g. 2-4 might be in one cell, 8-12 might be in another. In each case, the criteria would always be that the given input value either falls within the range of numbers in the cell, or it does not.

Hope this is clearly explained, and any help greatly appreciated.

Paul

bhickey

afrobeater wrote: »

So I figure one way of doing this is to put the following formula in B1:

=IF(AND(A1>=2,A1<=4),"1","0")

My question is, if I want to update that range (2 to 4) I need to step into the formula and edit it. Is there a way to have Excel automatically work out the range from a cell that simply has 2-4 (as text) written in it?

Assuming the formula wotks as is then if you put the number criteria in 2 different cells (say D1 & E1) then you could amend the formula as follows :

=IF(AND(A1>=$D$1,A1<=$E$1),"1","0")

The '$' symbols are important to make sure that those exact cells are always used as criteria even if the formula itself is copied to other cells.

afrobeater

Thanks bhickey, do you know how I might keep the number criteria in one cell, literally just entering, say, 2-4 in the cell, and have Excel parse out the numbers and put them in the formula?

bhickey

afrobeater wrote: »

Thanks bhickey, do you know how I might keep the number criteria in one cell, literally just entering, say, 2-4 in the cell, and have Excel parse out the numbers and put them in the formula?

Do you mean enter something like '2-4' in a cell and have Excel extract the numbers '2' and '4' from that cell for inclusion in a formula? I'm sure it could be done but why on earth would you want to do that? How would it help?

afrobeater

Yes, exactly that. It would help in terms of optical character recognized tables, trust me you don't wanna kno! But parsing would be of more help than have 2 separate cells to replicate one cell that just says 2-4. I want to do this for a large number of cells. Thanks in advance...

bhickey

afrobeater wrote: »

Yes, exactly that. It would help in terms of optical character recognized tables, trust me you don't wanna kno! But parsing would be of more help than have 2 separate cells to replicate one cell that just says 2-4. I want to do this for a large number of cells. Thanks in advance...

Okay then I'd still lean towards using C1 and D1 to hold the numbers. The C1 and D1 cells could however be set to extract these numbers from E1 which might contain the '2-4' value. So C1 would be everything before the '-' symbol and D1 would be everything after the '-' symbol. Is that feasible?

afrobeater

thanks again bhickey, not sure if that would work, as I would need to update the C1 and D1 cells for each "E1" type cell. Is there not a way to tell Excel to extract the number before (and after) the '-' symbol, and pop that into your formula?

Tails142

easy, see attachment

the formula you need are find, len, value, left, right

looks like this

=IF(AND(A9>=VALUE(LEFT(A1,FIND("-",A1)-1)),A9<=VALUE(RIGHT(A1,LEN(A1)-FIND("-",A1)))),1,0)

range in A1
number to evaluate in A9

bhickey

afrobeater wrote: »

thanks again bhickey, not sure if that would work, as I would need to update the C1 and D1 cells for each "E1" type cell. Is there not a way to tell Excel to extract the number before (and after) the '-' symbol, and pop that into your formula?

The whole point is that C1 and D1 would update themselves whenever E1 changes. If E1 is always of the format 'a-b' then

C1 : =LEFT(E1;FIND("-";E1)-1)
D1 : =RIGHT(E1;LEN(E1)-FIND("-";E1))

Replace the ';' characters with ',' for Excel. I'm using Openoffice so it uses semi-colons instead of commas in formulas.

You could also work the above into the original formula if you can't have a C1 and D1 as described.

afrobeater

thank you bhickey, and Tails - that's really helpful, thanks very much.

afrobeater

Tails142 wrote: »

easy, see attachment

the formula you need are find, len, value, left, right

looks like this

=IF(AND(A9>=VALUE(LEFT(A1,FIND("-",A1)-1)),A9<=VALUE(RIGHT(A1,LEN(A1)-FIND("-",A1)))),1,0)

range in A1
number to evaluate in A9

Hi Tails, thank you again for your help - I have one problem I have come up against. Your formula works well for almost everything I have thrown at it :-) except(!) the following:

If I have the occasional cell which just contains a single number (2) and not a range (12-25), can your formula be adjusted to handle a single or a double digit number on it's own, without any '-' sign, obviously without having to 'hard code' in that particular single/double digit number.

Many thanks in advance...

Tails142

Sure,

There are a couple of ways to skin this cat.

You could make the assumption that a 'range comparison' will always have a length of 3 digits or greater, and any time there is a single number to compare against, it will be of 2 digits or less, i.e. the number will have to be between 0 and 99.

Another way would be just to check if the cell contains the '-' symbol and deal with it accordingly. I prefer this way as then there is no limit on a single number comparison.

To do this we use the 'find' function, which either returns a number of where the character is, or an error. To let us work this way, then we must use the 'iserr' function aswell.

So basically, if the find doesnt come back as an error, do it the original way, if it does come back as an error, we want to compare to a single digit rather than a range.

=IF(ISERROR(FIND("-",A1)),IF(VALUE(A1)=A9,"Equal","Not Equal"),IF(AND(A9>=VALUE(LEFT(A1,FIND("-",A1)-1)),A9<=VALUE(RIGHT(A1,LEN(A1)-FIND("-",A1)))),"In Range","Not in Range"))

You should make sure that the format is set to 'text' of the cells where the range or single value is otherwise issues could arise.

Also I assumed if its a single digit you want it to be equal to that number, but you could change this bit if you want it to do something else. IF(VALUE(A1)=A9,"Equal","Not Equal")

afrobeater

Tails dude - that looks like it will work - let me give it a go and I'll feed back to you, but thank you so much, I've always found the folks on boards to be really generous and helpful.

afrobeater

Tails142 wrote: »

Sure,

There are a couple of ways to skin this cat.

You could make the assumption that a 'range comparison' will always have a length of 3 digits or greater, and any time there is a single number to compare against, it will be of 2 digits or less, i.e. the number will have to be between 0 and 99.

Another way would be just to check if the cell contains the '-' symbol and deal with it accordingly. I prefer this way as then there is no limit on a single number comparison.

To do this we use the 'find' function, which either returns a number of where the character is, or an error. To let us work this way, then we must use the 'iserr' function aswell.

So basically, if the find doesnt come back as an error, do it the original way, if it does come back as an error, we want to compare to a single digit rather than a range.

=IF(ISERROR(FIND("-",A1)),IF(VALUE(A1)=A9,"Equal","Not Equal"),IF(AND(A9>=VALUE(LEFT(A1,FIND("-",A1)-1)),A9<=VALUE(RIGHT(A1,LEN(A1)-FIND("-",A1)))),"In Range","Not in Range"))

You should make sure that the format is set to 'text' of the cells where the range or single value is otherwise issues could arise.

Also I assumed if its a single digit you want it to be equal to that number, but you could change this bit if you want it to do something else. IF(VALUE(A1)=A9,"Equal","Not Equal")

... Tails - just gave it a go and that worked really well. Thanks again!