Can anyone breakdown this formula?

rogcar

New Member
Joined
Aug 30, 2014
Messages
19
I have this formula:
Code:
{=IFERROR(INDEX(First,SMALL(IF((Owner="Rog")*(Roster="MLB")*(RIGHT(Pos,1)<>"P"),ROW(First)-MIN(ROW(First))+1),ROW(1:1))),"")}

I know its a CSE formula, but i need it broke down to where i can understand it.

The reason i need it to edit is i need to change the "Owner=?".

1 - Can someone breakdown this Formula?
IFERROR
(Index
(First,Small
If
*
* again
(Right
(Pos,1)
<>P)
,ROW
(First)
-MIN
(ROW(First))+1
,ROW(1:1)))
,""

2 - How do you copy a workbook and all of its formulas and values to a new workbook?

3 - Also how can you edit CSE formula's in the formula bar?
(The reason i need it to edit is i need to change the "Owner=?")

Thanks for any help. :(
 
Last edited:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
=IFERROR(INDEX(First,SMALL(IF((Owner="Rog")*(Roster="MLB")*(RIGHT(Pos,1)<>"P"),ROW(First)-MIN(ROW(First))+1),ROW(1:1))),"")
The IFERROR part means if the formula doesn't evaluate correctly it will return the value all the way at the end of the formula just inside the last parentheses "", which means a blank cell.
The INDEX part will return value from a range of cells given a ROW and COLUMN number. The area this formula will be returning the value from is First which I am assuming is a named range.
The SMALL gives the smallest number based on a list of numbers, it has an argument called K that asks which of the smallest values do you want. If K is 1, you get the smallest number in the group if K is 2 you get the second smalles number etc.
The number SMALL returns in this particular formula is the row number for the INDEX function, there is no column number being returned which means the First the INDEX part of the formula refers to is only 1 column wide.
The IF part is how you are getting the numbers for the SMALl function. For every cell where Owner = "Rog" you are getting a 1 and for every cell where is doesn't you are getting a 0.
Everywhere Roster = "MLB" , you get a 1 if it does and 0, if it doesn't, same thing for Right POS,1<>"P" and as you can see these are all being multiplied times each other.
Remember anynumber * 0 evalutes to 0, so every time in these ranges where they are not all equal to 1, you will get a 0 after the multiplication is finished.
The ROW(First)-MIN(ROW(First))+1 is a trick to give you a list of numbers starting at 1 through however many rows are in the range First for every one of these numbers that corresponds to a 1 returned
by the IF function you will get a row number returned to give to the small function. The ROW(1:1) will return a 1 to the K part of the small function to give you the smallest number returned.
When you copy the formula down that ROW(1:!) will change to ROW(2:2) changing the K value in small to 2 then 3 etc.

<colgroup><col span="24"></colgroup><tbody>
</tbody>
 

rogcar

New Member
Joined
Aug 30, 2014
Messages
19
Thanks Bruce for detailed explanation, i needed that very much.

I was wondering why when i copy this workbook with this formula in the new workbook the formula doesn't work out, i changed the names in the owner column to help and also changed the roster column to help it out, but it still doesn't work, i have done the CSE part of it also but no luck, any ideas, the workbook is really too big to post attachment, just wondering if you had any idea what i was doing wrong?

Thanks
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
Thanks Bruce for detailed explanation, i needed that very much.

I was wondering why when i copy this workbook with this formula in the new workbook the formula doesn't work out, i changed the names in the owner column to help and also changed the roster column to help it out, but it still doesn't work, i have done the CSE part of it also but no luck, any ideas, the workbook is really too big to post attachment, just wondering if you had any idea what i was doing wrong?

Thanks

Well I don't know how familiar you are with named ranges, but there are several in your formulas and if you are simply pasting the formulas from one workbook into another then that is probably where your problem lies.
First, Owner, Roster, Pos all appear to be named ranges, so I would start there.

You can also step through the formula using formula evaluator and see what each part of the formula is returning for some clues as to what the problem is.

You could post a small sample of your data using Excel Jeanie so we could see what the layout is. Just tell us what you want to do with the data, meaning what parameters you want to use and what you would expect the results to be and I'm sure I or someone else can help you figure it out.

Link:
Excel Jeanie Html
 
Last edited:

rogcar

New Member
Joined
Aug 30, 2014
Messages
19

ADVERTISEMENT

Ok thanks Bruce, i will continue to work on it.
Named Ranges yes that could be the problem, i just copy and paste, but i also define names in the name manager, maybe their is something i am doing wrong in that area.
Thanks
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
By the way, the formula you have is risky.

It's better to anchor a formula like this one to the cell where you invoke it. Let's say that you do so in A2, then the formula becomes:
Rich (BB code):
=IFERROR(INDEX(First,SMALL(IF((Owner="Rog")*(Roster="MLB")*(RIGHT(Pos,1)<>"P"),
  ROW(First)-MIN(ROW(First))+1),ROWS($A$2:A2))),"")
The following version would be a tad faster/transparent:
Rich (BB code):
=IFERROR(INDEX(First,SMALL(IF(Owner="Rog",IF(Roster="MLB",IF(RIGHT(Pos,1)<>"P",
  ROW(First)-ROW(INDEX(First,1,1))+1))),ROWS($A$2:A2))),"")
 

rogcar

New Member
Joined
Aug 30, 2014
Messages
19

ADVERTISEMENT

Hi Aladin
I used the 2nd formula and everything worked.
By the way what makes my formula risky?
Just wondering so maybe I don't make the same mistake down the road somewhere.
Thanks to Skywriter for the detailed explanation.
Thanks for the formula fix, Both of you are appreciated.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Hi Aladin
I used the 2nd formula and everything worked.
By the way what makes my formula risky?
Just wondering so maybe I don't make the same mistake down the road somewhere.
Thanks to Skywriter for the detailed explanation.
Thanks for the formula fix, Both of you are appreciated.

If you insert one or more rows in front of the row housing the formula with

ROW(1:1)

you'll see the value of this expression change, so feeding INDEX with wrong row/position information, the whole leading to wrong results.
 

rogcar

New Member
Joined
Aug 30, 2014
Messages
19
Hi Aladin
Thanks for the response, and the explanation, I see what your saying.
Thanks for all Your help, truly appreciated.
Rog
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,308
Members
409,862
Latest member
lbisacca
Top