Trying to understand IF(INDEX(FREQUENCY Formula

[Kevin_Nugent]

Dear Forum:


Windows 7 64-bit
Excel 2000


Mine is not a problem but a request for an explanation of a working formula.


Background...
I'm using Excel to track my monthly EZ-Pass transactions. I Copy them from the EZ-Pass website and Paste them into Excel.


I created a workbook with a main page displaying a table of charges, fees, running bals, etc. along with a graph of the monthly charges.


I created separate monthly pages: Jan, Feb, Mar and so on thru to Dec so as to Copy & Paste the data from EZ-Pass.


I defined a Named Range "Txn_ALL" to =Jan:Dec!$C:$C.


Txn_ALL contains the cells that receive my account number 123456789 (not my real account number) from the Pasted EZ-Pass data.


My desire was that if my account number were to be Pasted into 'Txn_ALL' then the account number would be displayed in a cell on the main page, else 'No Txns' would be displayed.


This is the formula that I was provided with:


=IF(INDEX(FREQUENCY(Txn_ALL,123456789-{0.5;0}),2),123456789,"No Txns")


I have a fairly good understanding of the IF function.
I've been reading up on INDEX and FREQUENCY in Excel's help.


Two questions:
1. How does the equation 123456789-{0.5;0} restrict FREQUENCY to finding ONLY 123456789 from within Txn_ALL? The way I understand it, this equation represents the Bins_array but I'm having trouble understanding the relevance of the -{0.5;0} portion of it and it's influence on the outcome.


2. Why does INDEX need to use '2' as row_num and not '1' as row_num?


It has been a fun project building this spreadsheet. I just wish I understood a little better how this formula works.


Thank you in advance for any insights.


-Kevin N.

 

[CharlesChuckieCharles]

Not sure why frequency is used at all

Its normally used as an Array Formula item

seems =IF(COUNTIF(TXN_ALL,"123456789")<1,"No Txns","123456789")
is all you need

 

[Kevin_Nugent]

Hi CharlesChuckieCharles,

Thank you for your reply.

After entering the suggested formula:

=IF(COUNTIF(TXN_ALL,"123456789")<1,"No Txns","123456789")

I receive a #VALUE! error.

COUNTIF does allow for named ranges but I'm wondering if Txn_ALL qualifies as multiple ranges which COUNTIF does not support, hence the #VALUE! error.

-Kevin N.

 

[Kevin_Nugent]

Hi CharlesChuckieCharles,

Playing around with this a little more...

I created a Named Range 'Txn_Jan' to =Jan!$C:$C

The formula =IF(COUNTIF(Txn_Jan,"123456789")<1,"No Txns","123456789") does indeed work when 123456789 is entered into the 'C' column of the 'Jan' page.

My take away from this is that since 'Txn_ALL' points to multiple pages (Jan:Dec) it constitutes multiple ranges and thus throws the #VALUE! error. Whereas 'Txn_Jan' points to a single range therefore allowing COUNTIF to perform as expected.

I was given the "FREQUENCY" formula several years ago and I can not remember from where or whom. I should have asked them for a brief explanation but I was just glad to have it working that I didn't want to look a gift horse in the mouth.

Thanks again for your reply.

-Kevin N.

 

[Aladin Akyurek]

Dear Forum:


Windows 7 64-bit
Excel 2000


Mine is not a problem but a request for an explanation of a working formula.


Background...
I'm using Excel to track my monthly EZ-Pass transactions. I Copy them from the EZ-Pass website and Paste them into Excel.


I created a workbook with a main page displaying a table of charges, fees, running bals, etc. along with a graph of the monthly charges.


I created separate monthly pages: Jan, Feb, Mar and so on thru to Dec so as to Copy & Paste the data from EZ-Pass.


I defined a Named Range "Txn_ALL" to =Jan:Dec!$C:$C.


Txn_ALL contains the cells that receive my account number 123456789 (not my real account number) from the Pasted EZ-Pass data.


My desire was that if my account number were to be Pasted into 'Txn_ALL' then the account number would be displayed in a cell on the main page, else 'No Txns' would be displayed.


This is the formula that I was provided with:


=IF(INDEX(FREQUENCY(Txn_ALL,123456789-{0.5;0}),2),123456789,"No Txns")


I have a fairly good understanding of the IF function.
I've been reading up on INDEX and FREQUENCY in Excel's help.


Two questions:
1. How does the equation 123456789-{0.5;0} restrict FREQUENCY to finding ONLY 123456789 from within Txn_ALL? The way I understand it, this equation represents the Bins_array but I'm having trouble understanding the relevance of the -{0.5;0} portion of it and it's influence on the outcome.


2. Why does INDEX need to use '2' as row_num and not '1' as row_num?


It has been a fun project building this spreadsheet. I just wish I understood a little better how this formula works.


Thank you in advance for any insights.


-Kevin N.

1. FREQUENCY can cope with 3d range specs; hence its usage justified.

2. The formula tacitly assumes integers in Txn_ALL.

3. In:

=IF(INDEX(FREQUENCY(Txn_ALL,123456789-{0.5;0}),2),123456789,"No Txns")

the 123456789-{0.5;0} bit creates the following bins:

{123456788.5;123456789}

4. Any numeric occurrence/token < 123456789 is sorted into the 123456788.5 bin; any occurrence = 123456789 into the 123456789 bin; any occurrence > 123456789 into the basket > bin.

5. If you just pick out the FREQUENCY bit and apply F9, you would get an array of results which just consists of 3 counts:

{4;1;2}

meaning: There is 4 tokens obeying the < condition; 1 obeying the = condition; and 2 obeying the > condition.

6. The array of results such as foregoing is fed to the surrounding INDEX...

INDEX({4;1;2},2)

which says: Return the second count, i.e., 1.

7. If INDEX returns a count >= 1, the surrounding interprets that as a TRUE message and delivers 23456789, otherwise No Txns.

Hope this helps.

 

[Kevin_Nugent]

Hi Aladin Akyurek,

WOW! Absolutely brilliant!

Thank you so much for your excellent and in-depth description of the events.

I can honestly say that I understand completely now what is going on thanks to your step-by-step breakdown.

Thank you, thank you, thank you,

-Kevin N.

 

[Aladin Akyurek]

Hi Aladin Akyurek,

WOW! Absolutely brilliant!

Thank you so much for your excellent and in-depth description of the events.

I can honestly say that I understand completely now what is going on thanks to your step-by-step breakdown.

Thank you, thank you, thank you,

-Kevin N.

Glad to help.

 

[CharlesChuckieCharles]

Yes I forgot about the 3D array limitations where by only COUNT and COUNTA work


see this

https://support.office.microsoft.co...22f-ac2a-c00e0e09c9d1&ui=en-US&rs=en-GB&ad=GB

Doesn't explicitly mention FREQUENCY but since when has Microsoft help been helpful

 

[Kevin_Nugent]

Hi CharlesChuckieCharles & Aladin Akyurek,

I've learned something new from both of you.

I've always said that if you stop learning, you stop living.

So I live on for another day.

Thanks again to you both.

Regards,

-Kevin N.