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:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
=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>
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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))),"")
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Hi Aladin
Thanks for the response, and the explanation, I see what your saying.
Thanks for all Your help, truly appreciated.
Rog
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top