Excel Formula - Number within range..{Resolved, and then some}

[Spajeoly]

So we all know that this would be easy in VBA.

However, what I was thinking of doing for a change is using a VLookUp() in this table I have.

The one thing I would need to do though is see if the number in Cell A is within the number range in the table array.

So you have say the number 32 in cell a and in the table array you have:

20 - 24
25 - 29
30 - 34
35 - 39
40 - 44
45 - 49
50 - 54
55 - 59
60 - 64

Think there's a reliable way to return row 3 with a vlookup-type formula?

Thanks for looking.

[Siddharth Rout]

Hi Spajeoly

I couldn't get you query? What are you exactly trying to do?

[Spajeoly]

Ok so if I have a cell with the value of 32. I want to do a VLookup based on that value. However, my table array has number ranges, not individual numbers like so:

20 - 24
25 - 29
30 - 34
35 - 39
40 - 44
45 - 49
50 - 54
55 - 59
60 - 64

In order to do a VLookup I would need Excel to know that 32 is in the range of 30 - 34.

I think I am chasing my tail with this though.

[Siddharth Rout]

Oh Ok I get it

No I think it is not feasible to do it. even if we had to use we will have to use Hlookup instead of vlookup. Secondly you need to specify row numbers after the table array to get the result which will defeat the entire purpose.

This can be easily done in vba using user defined functions. do you want that?

[Spajeoly]

Oh, it's easy in VBA, and doing it in a formula would be using Both VLookup & HLookup. I played with that already. But unless I want to expand the number range into a table with every number then it's just not worth it. I have already coded my checking out in VBA.

I have more VBA code than I can handle in my current state of mind...

Ugh.

[Siddharth Rout]

yes it is true but then a simple user defined function is much more easier than writing a complex excel formula?

[Spajeoly]

Well that depends, sometimes formulas have their benefit over the functions. I often just weigh which is likely to be easiest. Most of the time I do VBA just because I enjoy being able to control what is done in what order etc etc etc.

And I'm mostly retarded.

[westconn1]

is 30 - 34 the actual cell content or? there was a thread the other day that should adapt to this

[Spajeoly]

Yes, that is the cell value, or text or whatever.

I think I could do it, but, my brain has melted for the day.

[anhn]

No I think it is not feasible to do it.

You may say I am a devil or monster.
---------

An array formula can help. (Raise your hand if you don't know how to enter an array formula.)

The formula used
{=OFFSET($B$2,MATCH($D2,VALUE(LEFT($B$2:$B$10,FIND(" ",$B$2:$B$10)-1)),1)-1,0)}
can also be written as:
{=INDEX($B$2:$B$10, MATCH($D2,VALUE(LEFT($B$2:$B$10,FIND(" ",$B$2:$B$10)-1)),1))}

If min and max numbers of all number range always have 2 digits then the formula can be shorten by replacing FIND(" ",$B$2:$B$10)-1 with number 2:
{=OFFSET($B$2,MATCH($D2,VALUE(LEFT($B$2:$B$10,2)),1)-1,0)}
or
{=INDEX($B$2:$B$10, MATCH($D2,VALUE(LEFT($B$2:$B$10,2)),1))}

[westconn1]

very nice anhn

[anhn]

very nice anhn

Thanks pete. I did that because "Yes, I think it is feasible to do it."

Correction: The formulas in post#10 will give wrong result if D2 > 64. It should be changed to:
{=IF($D2>VALUE(RIGHT($B$10,2)),NA(),INDEX($B$2:$B$10, MATCH($D2,VALUE(LEFT($B$2:$B$10,2)),1)))}

[Colin_L]

Assuming:
(1) you have your ranges in the B column --> Named range called SPREAD
(2) you have your 'numbers to check' in the D column
both as exemplified by post #10...

This formula will also return the relevant range. It doesn't have to be entered as an array formula. If the look up value is less than the minimum range then it returns an error; if the look up value is greater than the maximum range then it returns the maximum range. The logic could be extended to account for these extremes, but to keep it simple I have left it there. It also assumes there are no numerical gaps or overlaps between each set of ranges.

=INDEX(SPREAD,-SUMPRODUCT(-(-(LEFT(SPREAD,FIND(" ",SPREAD)-1))>=(-D2))))

And, similarly to Anhn's formula, if the ranges are always between 11-99 then this part of the expression:
FIND(" ",SPREAD)-1
can be replaced with a '2':
=INDEX(SPREAD,-SUMPRODUCT(-(-(LEFT(SPREAD,2))>=(-D2))))

Colin

[anhn]

=INDEX(SPREAD,-SUMPRODUCT(-(-(LEFT(SPREAD,2))>=(-D2))))

It's nice too, but with D2 < 20, that will return "20 - 24" instead of #N/A (at least with Excel-2007); with D2 > 64 that will return "60 - 64".
I used SUMPRODUCT to build the formula but faced INDEX($B$2:$B$10, 0) = INDEX($B$2:$B$10, 1) = "20 - 24"
So I switched to MATCH() function.

Your formula will be hard to understand on multiple "-", that can be rewritten as:
=INDEX(SPREAD,SUMPRODUCT(1*(0+LEFT(SPREAD,2)<$D2)))
(a) 1* is critical for SUMPRODUCT to work in this case.
(b) Instead of VALUE(LEFT(SPREAD,2)) as I used, that can be 0+LEFT(SPREAD,2)

[Colin_L]

Hi Anhn,

Thanks for your reply and for picking up on that.

Regarding this:

Your formula will be hard to understand on multiple "-", that can be rewritten as:
=INDEX(SPREAD,SUMPRODUCT(1*(0+LEFT(SPREAD,2)<$D2)))
(a) 1* is critical for SUMPRODUCT to work in this case.
(b) Instead of VALUE(LEFT(SPREAD,2)) as I used, that can be 0+LEFT(SPREAD,2)

I think the subject of explicitly converting types vs various methods of coercion is an interesting one, and something which I have done a fair amount of research on.

Using negation (-) is a more efficient method of coercion than using multiplication (*) and/or addition (+), which is why I prefer it. I agree that it could be more confusing to the eye when reading the formula - but I think it's just as likely that someone's going to ask why adding a zero and multiplying by one (both in your adjusted solution) are necessary:

1*(0+LEFT

as someone asking why using negation is necessary. Once it is understood that we are just converting boolean or text values into their numerical equivalents, neither is more daunting nor less intelligible than the other. Also, the use of negation has been heavily promoted (because it is more efficient) across online forums so it is now a very well accepted and established - even popular - method.

So, on the grounds that the formula is more efficient using negation and that the logic behind negation, multiplication or addition are all the same and equally understandable, I think that using negation is preferable.

Colin

[anhn]

Colin, I have different thought on what you said:

1. In Excel formula, TRUE = 1 (not -1 as in VB/VBA) so -TRUE = -1 and -(-TRUE) = TRUE = 1. And why -x >= -y but not x <= y or y >= x ?

2. I already said why use 1* that will allow SUMPRODUCT() to work. That use a hidden syntax of SUMPRODUCT(a*b*c) instead of SUMPRODUCT(a,b,c)

3. I already explained why I use 0+LEFT(...). That is a short way to convert a numeric text to number in Excel formula instead of using VALUE(LEFT(...)).
A similar way to convert number X to a text value is : =""&X

[Colin_L]

Hi Anhn,

2. I already said why use 1* that will allow SUMPRODUCT() to work. That use a hidden syntax of SUMPRODUCT(a*b*c) instead of SUMPRODUCT(a,b,c).

My thought is very strongly that it's not using a 'hidden syntax' and there is no cross-over between the two syntaxes.

Syntax SUMPRODUCT(Arg1,Arg2)
These all return a result of 2:

1. =SUMPRODUCT(0+(A1:B1=A1:B1),0+(A1:B1=A1:B1))
2. =SUMPRODUCT(1*(A1:B1=A1:B1),1*(A1:B1=A1:B1))
3. =SUMPRODUCT(--(A1:B1=A1:B1),--(A1:B1=A1:B1))

So you can see that using 1* is not yielding the 'hidden syntax', otherwise the other two methods wouldn't work.

In all cases, the boolean result array of the evaluation A1:B1=A1:B1 is {TRUE;TRUE}, and are coerced into numerical arrays by performing a mathematical operation on them:
A1:B1=A1:B1 ---> --(A1:B1=A1:B1)
{TRUE;TRUE} ---> {1;1}

These numerical result arrays are then passed into the SUMPRODUCT function. So each of these three formulas is the equivalent of:
=SUMPRODUCT({1;1},{1;1}) ---> result of 2.

Syntax SUMPRODUCT(Arg1)
With the direct multiplication you suggested:
=SUMPRODUCT((A1:B1=A1:B1)*(A1:B1=A1:B1))
you are just simply directly coercing the result arrays.
A1:B1=A1:B1 * A1:B1=A1:B1 ---> (A1:B1=A1:B1)*(A1:B1=A1:B1)
{TRUE;TRUE} * {TRUE;TRUE} ---> {1;1}

This numerical result array is then passed as an argument into the SUMPRODUCT function. So that formula is the equivalent of:
=SUMPRODUCT({1;1}) --> result of 2

Which is better?
Extensive testing has shown that this formula:
=SUMPRODUCT(--(A1:B1=A1:B1),--(A1:B1=A1:B1))
is faster than this formula:
=SUMPRODUCT((A1:B1=A1:B1)*(A1:B1=A1:B1))

We can also optimise the formula further by taking advantage of the fact that -1 * -1 = +1, and reduce the number of negation operations:
=SUMPRODUCT(-(A1:B1=A1:B1),-(A1:B1=A1:B1))
which equates to =SUMPRODUCT({-1;-1},{-1;-1}) which gives a result of 2.
(This addresses point #1 on your reply).

3. I already explained why I use 3. I already explained why I use 0+LEFT(...). That is a short way to convert a numeric text to number in Excel formula instead of using VALUE(LEFT(...)).

Right, and I've explained, any mathematical operation will coerce the numeric text that represents a number to a numerical type:
0+LEFT(...)
1*LEFT(...)
LEFT(...)-0
--LEFT(...)
(LEFT(...))^1

Of these mathematical operations, negation binds the tightest and is the most efficient.

HTH,
Colin

[anhn]

Right, and I've explained, any mathematical operation will coerce the numeric text that represents a number to a numerical type:
0+LEFT(...)
1*LEFT(...)
LEFT(...)-0
--LEFT(...)
(LEFT(...))^1

Of these mathematical operations, negation binds the tightest and is the most efficient.

I don't want to get into this argument. We are on off topic so far.
Stay with what you thought. Keep using -- if you love it.

'-- THIS CODE IS IN AN EXCEL MODULE
Sub Test()
   Dim t As Single
   Dim i As Long
   Dim x As Variant, y As Variant
   
   x = 2.35
   
   t = Timer
   For i = 1 To 10 ^ 7:  y = --x:  Next
   Debug.Print "--x : "; (Timer - t); "secs"

   t = Timer
   For i = 1 To 10 ^ 7:  y = 0 + x:  Next
   Debug.Print "0+x : "; (Timer - t); "secs"

   t = Timer
   For i = 1 To 10 ^ 7:  y = 1 * x:  Next
   Debug.Print "1*x : "; (Timer - t); "secs"

   t = Timer
   For i = 1 To 10 ^ 7:  y = x - 0: Next
   Debug.Print "x-0 : "; (Timer - t); "secs"

End Sub

--x :  0.671875 secs
0+x :  0.484375 secs
1*x :  0.4375 secs
x-0 :  0.359375 secs

Noted: Above are operations in VBA code.
With worksheet formulas that can be optimized by Excel, the speed of the below formulas are almost identical with =VALUE(...) is slightly slower of 1%.
=--LEFT($B2,2)
=0+LEFT($B2,2)
=1*LEFT($B2,2)
=LEFT($B2,2)-0
=VALUE(LEFT($B2,2))

[Spajeoly]

Nicely done, Anhn.

I figured it was possible outside of VBA. My problem is I do everything in VBA too often so my formula knowledge is somewhat elementary.

Thanks.

[Siddharth Rout]

Nice one Anhn.

but seems like my post#6 went unnoticed

[Colin_L]

Hi Anhn,

Thanks for your reply.

Since it is straying 'off-topic' I will keep this as brief as I can whilst trying to clearly tie up the loose ends - since this will be my last post on this thread. I do so for the benefit of other members who will be reading this and may find it of interest! It is, after all, an extremely interesting, productive discussion and the sharing of information/bouncing around of ideas is exactly the purpose of the forums.

Regarding your testing.
By all means, use the VBA Timer to measure the time taken. The test results of the coercions in VBA are certainly off-topic - we had strayed into discussing data type coercions within formulas. Coercion in VBA itself is a completely different kettle of fish.

So let's test the calculation speed of the actual sumproduct formulas with the VBA timer....

With worksheet formulas that can be optimized by Excel, the speed of the below formulas are almost identical with =VALUE(...) is slightly slower of 1%.

Right, when coercing a single text/boolean data type into a numerical data type, the timing difference with the different methods of coercion is small. But, we're talking about arrays here. Typically a sumproduct formula could contain two or three arrays, each with 60,000+ elements. A worksheet might contain 10, 20 or even 100 of these formulas. So once you start coercing 180,000 elements per formula and there are say, a hundred of these formulas in the worksheet, the small timing difference of coercing each element in these arrays becomes extremely significant... tiny amount x 180,000 x 100 = A lot.

Here's some simple testing with the VBA timer. I timed the calculation speed of a worksheet with 20 of each formula and I took the average time of 200 tests.

Negation
=-SUMPRODUCT(-($A$2:$A$65536=$A$2:$A$65536),-($B$2:$B$65536=$B$2:$B$65536),-($C$2:$C$65536=$C$2:$C$65536))
Average time taken: 1.42984 seconds

Multiplication Syntax (Arg1)
=SUMPRODUCT(($A$2:$A$65536=$A$2:$A$65536)*($B$2:$B$65536=$B$2:$B$65536)*($C$2:$C$65536=$C$2:$C$65536))
Average time taken: 1.61729 seconds

Addition
=SUMPRODUCT(($A$2:$A$65536=$A$2:$A$65536)+0,($B$2:$B$65536=$B$2:$B$65536)+0,($C$2:$C$65536=$C$2:$C$65536)+0)
Average time taken: 1.83963 seconds

Multiplication Syntax (Arg1,Arg2)
=SUMPRODUCT(($A$2:$A$65536=$A$2:$A$65536)*1,($B$2:$B$65536=$B$2:$B$65536)*1,($C$2:$C$65536=$C$2:$C$65536)*1)
Average time taken: 1.84518 seconds

Exponentiation
=SUMPRODUCT(($A$2:$A$65536=$A$2:$A$65536)^1,($B$2:$B$65536=$B$2:$B$65536)^1,($C$2:$C$65536=$C$2:$C$65536)^1)
Average time taken: 2.84664 seconds

Of course, the actual average timing difference will vary for different users, but the ratios should be similar.

Perhaps (in this case with 20 formulas) saving a second each time a worksheet is calculated is insignificant for most people. In my industry, it is very significant. The bottom line is that they all give the correct answer, so, if one has reasoned that sumproduct is to be used in the first place, I can't see any reason to use anything but the fastest version since there is nothing else to separate them?

Stay with what you thought. Keep using -- if you love it.

It's not a question of love? I have explained the reasons why I use it - so of course I will continue to do so! If you 'love' using addition or multiplication, then carry on using them but please don't tell me that I - or others - should also because, as I have shown, there's no good reason to. It was you, after all, who first told me I should change the way I coerce data types [post #14], and not the other way round. I have been simply defending my methodolody...

Colin

[Spajeoly]

Yeah, and that was brief. So I wouldn't push it.

Thanks for all the replies everyone.

[anhn]

Nice one Anhn.

but seems like my post#6 went unnoticed

Thanks mate!
For sure, the OP knows how to write UDF for this as he said. We are trying to build on cell formula.
If that's me, I will design the spreadsheet a little bit different to make life easier.

[anzes]

You may say I am a devil or monster.
---------

An array formula can help. (Raise your hand if you don't know how to enter an array formula.)

The formula used
{=OFFSET($B$2,MATCH($D2,VALUE(LEFT($B$2:$B$10,FIND(" ",$B$2:$B$10)-1)),1)-1,0)}
can also be written as:
{=INDEX($B$2:$B$10, MATCH($D2,VALUE(LEFT($B$2:$B$10,FIND(" ",$B$2:$B$10)-1)),1))}

If min and max numbers of all number range always have 2 digits then the formula can be shorten by replacing FIND(" ",$B$2:$B$10)-1 with number 2:
{=OFFSET($B$2,MATCH($D2,VALUE(LEFT($B$2:$B$10,2)),1)-1,0)}
or
{=INDEX($B$2:$B$10, MATCH($D2,VALUE(LEFT($B$2:$B$10,2)),1))}

Hello,

I have an exact problem as it is described in thread topics, I tried this formula (=INDEX($B$2:$B$10;MATCH($D2;VALUE(LEFT($B$2:$B$10;2));1)) and all I get is #N/V
It looks like the problem is in MATCH part, but I can't find the reason.

I entered it as an array formula and also replaced , with ; in formula.
I'm using Excel 2007 and I used the same sample data as in this case.

Anyone with any suggestion?
BR A.