Explain Formula

[Reji Rajan]

Hi All,

I came across one formula in one of the workbooks my colleague shared. He doesn't know the origin of the workbook or the guy who made the workbook hence thought of seeking help here.

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(INDEX($E$3:$E$403, MATCH(0, COUNTIF($L$4:L4, $E$3:$E$403)+($E$3:$E$403=""), 0)), INDEX($F$3:$F$403, MATCH(0, COUNTIF($L$4:L4, $F$3:$F$403)+($F$3:$F$403=""), 0))), INDEX($G$3:$G$403, MATCH(0, COUNTIF($L$4:L4, $G$3:$G$403)+($G$3:$G$403=""), 0))),INDEX($H$3:$H$403, MATCH(0, COUNTIF($L$4:L4, $H$3:$H$403)+($H$3:$H$403=""), 0))), INDEX($I$3:$I$403, MATCH(0, COUNTIF($L$4:L4, $I$3:$I$403)+($I$3:$I$403=""), 0))),"")

I just want to know if this formula is correct & if it is correct, what exactly is it supposed to return as result.

Please help & do let me know in case i have to attach the worksheet for better clarity.

Regards,
RJ

[offthelip]

The whole formula looks wrong to me, one particular error is the all the countifs are the wrong way round, the format for countif is (Range,Criteria)
So COUNTIF($L$4:L4, $F$3:$F$403)
should be

COUNTIF($F$3:$F$403,$L$4:L4 )

, so the formula is incorrect.
I have no idea what the composer was intending,
It reminds me of the useful comment found in a bit of particuarly difficult code:

" When I wrote this code only God and I knew how it works, now God alone knows"

[Reji Rajan]

Hi,

Firstly thank you for the reply & sorry to revert so late. I want to rework the sheet, so need help.

I have date & time wise report of shows. The show start time & end time for a particular day is 06hrs to 25hrs. Every show has 3 to 5 intervals. My job is decide what data i fill in those intervals. for e.g, in show 1, break 1 will have data "A", break 2 will have data F, so on. alongside each day, i want to populate all the cell values for each day & their count. for e.g on 12/12/17, i have used A, B, C, D, F & G, so these values along with their count should get displayed. This needs to be done for all days. Want to know if this is possible & without using VBA. Kindly help with any suggestions & ideas.

I have attached sample sheet for reference.

[Aussiebear]

"The show start time & end time for a particular day is 06hrs to 25hrs" . Surely you recognise that there is only 24 hours in a "particular" day

[Aflatoon]

Why does your sample sheet appear to bear no relation to the formula you posted?

[offthelip]

Your requirements are unclear so I have no idea what you are asking for.

[Aussiebear]

After looking at this formula and the supplied workbook, I was wondering if the OP was trying to do the following;

1. IFERROR(INDEX($E$3:$E$403,MATCH(COUNTIFS($E$3:$E$403,$L$4,$E$3:$E$403,”=””” ),0).

Since Countif as explained earlier requires the format =COUNTIF(Range, Value) then the following made no sense "COUNTIF($E$3:$E$403, $L$4)+($E$3:$E$403,="")" unless the OP was attempting to countif two values in two ranges......, which requires the use of COUNTIFS, in any effort to return two values for the MATCH function.

2. Each sequenced IFERROR(INDEX(MATCH seems to be trying to determine if the value in $L$4 is to be found within the particular column that is nominated, yet the workbook is suggesting that the OP is seeking either a "Date" value or a "blank" value (given the formula that resides in cell L4), within a range of Alphabetical values... or am i reading this wrong?

3. The sequencing of IFERROR's is confusing as it seems as if it hides where a section of the formula fails.

[Reji Rajan]

Hi,

Apologies for not following up on the post, was away for sometime due to some family emergency, Anyways I managed to rework on the above query & have tried to simplify the same as much as possible. Basically, the workbook is used to make a grid for the promotions to be played daily in a week. The data is sourced from the system. I have managed to use macro to get that data on my working sheet, have attached the working file. The user will update the promotions from cell Cell E3:I3 to say E300:I300, the data is for a week starting from Tuesday to next Monday & as "Aussiebear" mentioned the start time for any particular day is 07hrs to 23hrs. My second code is ideally supposed to show me a pivot of the number of promotions used each day. I want to know if multiple pivots can be made on the same sheet (Grid sheet) at the start of each day, for e.g. at cell"M3", i get a pivot for 13th Mar which has source data E3:I26 with the name of promos in row field & their count in values field. like wise for each day at the first clock hour. If not a pivot, any formula which gives the same result. Also the Grid sheet can have more Breaks, for the time being i have defined 3 breaks (break1, break2 & break3) there can one or 2 breaks more. So the sheet should be dynamic enough to accommodate the additional breaks. I am not expecting an exact solution but just some help to make this Workbook work.

[sassora]

To make the problem easier to read, I've rewritten it:

The workbook is used to make a grid for the promotions to be played daily in a week. The user updates column E, the data is for a week starting each Tuesday, opening hours are 07hrs to 23hrs.

Pivot or otherwise:
Number of promotions used each day.
Name of promos in row field & their count in values field.
Display "pivots" throughout the sheet, at start of each day.
Need to accommodate more columns (breaks) as required.

I agree with your thought about using a pivot to summarise this information. I would advise against putting in as many tables in as there are weeks though! It seems neater to create a new tab with all data included in a single pivot table and filter to the week of interest. Perhaps add a "week starting on" and then have the Tuesday e.g. 13/03/2018, be the value for the whole week.

Another benefit of this is that you'll be able to keep track of longer term trends too.
If required you could use a little VBA to show the filter for the current week.

Hope this helps, I don't log in here much anymore but good luck.

[Reji Rajan]

Hi,

@sassora : thanks for rephrasing my query, its much easier to read now. I agreed with the thought of not putting many tables but i have come across another hurdle where I cant get a single pivot. The pivot only catches the first row of promotions & not the rest. All i want is Datewise split of the number of promotions along with their names used in the entire week. Something like below:

	13th	14th	15th	16th	17th	18th	19th
AAA	10	20	30	40	50	60	70
BBB	30	19	21	33	56	67	etc
CCC
DDD
EEE

[Reji Rajan]

Want to know if there is a way around this, the problem looks simple but am just not getting the desired result, wondering where exactly am i going wrong?

[sassora]

Perhaps attach the workbook? It sounds like the source range isn't correctly set.

Also, please explain where the workbook is different to your expectation and what you think the result should be for specific cases.

[Reji Rajan]

I have attached the sample data sheet from which either the pivot or the table can be designed.

• Column D to H will have values which can be repetitive, cells can be blank as well.
• Column I indicates the week start date. (its a weekly report & not monthly).
• Column K can indicate the pivot or the table which ideally should give me the names of the promotions used in the week & their count day wise.
• I have made a pivot but its incorrect. for e.g, there are 51 promotions used on 13th but the pivot says 25 coz its considering only the first column D & not the range.
• I searched the internet for solutions & found that index, match, iserror & count formula can be used at a complex degree to derive results but am not well versed with such formula.

[sassora]

I've attached the way I would approach this. Since pivot tables sum up in the column rather than across the row, the current layout wouldn't work very easily.

My suggestion is to use array formulas (Ctrl-Shift-Enter formulas), they take longer for Excel to calculate but the syntax is much simpler. Perhaps someone will write it in VBA for you. In usual formulas you just press Enter to evaluate them, here you press Ctrl-Shift-Enter simultaneously. This will give the formula a set of curly brackets, i.e. {= formula }.

I'll leave the rest to you. Hope this helps.

[Paul_Hossler]

Since there seems to be macros used, and since I think that pivot tables are more flexible, I made the data more pivot table friendly by building an intermediate list for the PT to use

Capture.JPG

Option Explicit

Sub BuildList()
    Dim wsList As Worksheet, wsData As Worksheet
    Dim rPromos As Range, rPromo As Range
    Dim o As Long

    Set wsData = Worksheets("Data")
    Set wsList = Worksheets("List")
    Set rPromos = wsData.Range("D:H").SpecialCells(xlCellTypeConstants, xlTextValues)
    
    
    wsList.Range("A:B").ClearContents
    
    o = 1
    wsList.Cells(o, 1).Value = "Date"
    wsList.Cells(o, 2).Value = "Promo"
    
    o = o + 1
    For Each rPromo In rPromos.Cells
        If rPromo.Row > 2 Then
            wsList.Cells(o, 1).Value = rPromo.EntireRow.Cells(1).Value
            wsList.Cells(o, 2).Value = rPromo.Value
            o = o + 1
        End If
    Next
    wsList.Cells(1, 1).CurrentRegion.Name = "ListData"
    wsList.PivotTables(1).PivotCache.Refresh
End Sub

[sassora]

Agreed if you change the format of the tables then pivot tables will work. Which ever method setting filling the array in VBA would be better rather than writing to the sheet.

To excel novices I would advise minimal code where possible.