Solved

Using "inverse arguments" in Excel formulas

Posted on 2012-04-06
3
277 Views
Last Modified: 2012-04-21
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
0
Comment
Question by:Steve_Brady
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 7

Expert Comment

by:leptonka
ID: 37817499
Hi,

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
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37818460
Let's say you have a column B having some numeric values in it, and you want to find the first occurrence of a value that is greater than 25 - e.g., what row it is on:

=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;100;77;1}

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

the 1/(B1:B15>25) would resolve to: {#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;1;1;1;#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:B15) - The lookup value is 2, which won't be found, so the last 1 is used, to return the month name from column E.  That's the neat thing about MATCH, LOOKUP, VLOOKUP, HLOOKUP - they all THINK that the data is sorted, so if you give it a large value to look for - and 2 is large enough for this particular comparison because of the 1/() math we give it, it returns the last occurrence of what we're looking for - always.

=LOOKUP(1,1/(B1:B15>25),B1:B15) - gives us the first occurrence of what we're looking for.  note we don't have to array enter the lookup function in these instances

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:B15)) - array entered - would resolve to 8 unique values.  Why:

The COUNTIF(B1:B15,B1:B15) resolves to {3;2;2;3;4;2;4;1;3;2;4;1;1;1;4}
and the 1/COUNTIF(B1:B15,B1:B15) resolves to {0.333333333333333;0.5;0.5;0.333333333333333;0.25;0.5;0.25;1;0.333333333333333;0.5;0.25;1;1;1;0.25}

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
0
 

Author Comment

by:Steve_Brady
ID: 37876565
Dave , superb answer:  clear, well organized, concise but comprehensive, and enjoyable to read.  Many thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question