Hello,

It seems that not infrequently, I see "inverse arguments" used in Excel formulas. By inverse, I mean 1/B1:B15 rather than simply B1:B15. Can someone explain the basic concepts underlying those types of arguments or point me to a source which does?

Thanks

It seems that not infrequently, I see "inverse arguments" used in Excel formulas. By inverse, I mean 1/B1:B15 rather than simply B1:B15. Can someone explain the basic concepts underlying those types of arguments or point me to a source which does?

Thanks

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

=MATCH(1,1/(B1:B15>25),0) 'control-shift-enter as its an array formula

So, the B1:B15>25 piece resolves to an array of TRUE/FALSES. The 1/(B1:B15>25) then resolves to an array of #DIV/0!'s or 1's. Thus, the match searching for an exact value of 1 will return the position of the first 1/1 or 1 in the column where the value is > 25.

So, if B1:B15 had the values: {3;4;2;3;1;2;1;50;3;4;1;26

the B1:B15>25 would resolve to: {FALSE;FALSE;FALSE;FALSE;F

the 1/(B1:B15>25) would resolve to: {#DIV/0!;#DIV/0!;#DIV/0!;#

And now, that array can be utilized by many a function:

=MATCH(1,1/(B1:B15>25),0) - array entered - would resolve to: 8, as that's the first 1 in the array. You could use INDEX/MATCH to return the first value (or the LOOKUP function - see below for last match example)

To find the LAST value in the column > 25, we can use the lookup function:

=LOOKUP(2,1/(B1:B15>25),B1

=LOOKUP(1,1/(B1:B15>25),B1

PS - there's also formulas to find the 2nd match, 3rd match, etc., but that wouldn't involve using the inverse approach so I'll skip that.

re: leptonka's example can also be written as:

=COUNT(1/(B1:B15>25)) would give us the number of values > 25, or 4

tho in this instance using SUMPRODUCT or COUNTIF would be more efficient and easier to ascertain by others. However, there is a use for this that is uniquely designed to use this type of setup:

Counting Unique instances in a range of cells!

=SUM(1/COUNTIF(B1:B15,B1:B

The COUNTIF(B1:B15,B1:B15) resolves to {3;2;2;3;4;2;4;1;3;2;4;1;1

and the 1/COUNTIF(B1:B15,B1:B15) resolves to {0.333333333333333;0.5;0.5

So summing that up, we get 8 - because 3 occurs 3 times, each 3 gets a value of 1/3, so is only counted once, the 4's occur 4 times, so each gets .25, and thus the 4's get counted once, etc.

And I'm sure there are more - check out that Contextures link and you'll find examples like this as well.

Bottom line, the 1/ARRAY resolves to TRUE/FALSES and thinking of ways you can use the 1's vs #DIV!0's or fractions (as in the last example) to ascertain what you're looking for via LOOKUP, MATCH, SUM, COUNT provides the fundamentals behind why these formulas are used. Some pretty creative thinkers came up with this initially, but it starts to come naturally if you think through it like you're doing - pretty soon, you'll be building these "from memory" as I try to, lol (though I spend so much time with VBA it takes me a while and if I don't get it I start googling then AH-HAH, now I remember!).

Dave

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I use it to count non-zero values, for example:

=COUNT(1/B1:B15) - confirm with Ctrl+Shift+Enter will give you the number of non-zero values (numbers) in the range, because 1/0=#DIV error.

(Yes, I know it could be done in different way, without array-entered formula. :-)

Cheers,

Kris