You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Formula, automaticall selecting a cost centre

Advertisement

Hi Tom

My problem is I need to be able to show cost centres in a summary if they have associated values greater than 14. I was hoping there was a way in which the cost centre and value could be transferred automatically from the detail to the summary.

eg

Cost Centre Value

A 5

B 4

C 7

D 15

E 3

F 18

G 1

In the Summary only the cost centres D and F need to show. I could bring the values down with a sumif, but getting the D & F to appear automatically is where I'm having trouble. For the example, Cost centre is Column A, Value is column B.

Any help you could give on this would be very much appreciated.

Best Regards

Paul

Paul,

I don't know how much you know about array formulas so I will provide a fairly low level explanation. If you are already proficient with them, then just scan to the bottom for the final formula suggested.

then in any cell in the same sheet put in this formula and hit enter

=IF($B$2:$B$8>14,ROW($B$2:$B$8))

no come back to that cell and select the formula in the formula bar. Hit F9. This will evaluate the section of the formula you have highlighted. Since you highlighted the whole formula it will show

{FALSE;FALSE;FALSE;5;FALSE;7;FALSE}

so the first false is the result of the IF test on the first cell in the specified range B2:B8. That is false since it did not pass the test of cell B2 being bigger than zero. Since we supplied a range of cells as the test

$B$2:$B$8>14

each is tested and either returns FALSE or if it is larger than 14, it returns the row number where it is located

Row($B$2:$B$8)

and we get the array I showed.

{FALSE;FALSE;FALSE;5;FALSE;7;FALSE}

Then hit the escape key (esc) to restore the formula. If you hit enter, the above array will replace the formula.

we can use the small function to pull out the numerical values from the array.

Small(IF($B$2:$B$8>14,ROW($B$2:$B$8)),1) would return the smallest number (row number 5) and

Small(IF($B$2:$B$8>14,ROW($B$2:$B$8)),2) would return the next smallest number (row number 7)

We can use row(A1) to return the 1 as the second argument to Small and then if we drag fill that formula down, the A1 will become A2 in the next cell down and row(A2) will produce the 2 we need as the second argument in the next cell.

The only qualification is that we need to array enter the formula in the first cell.

Let's say we are in cell D3. In D3 enter

=Small(IF($B$2:$B$8>14,ROW($B$2:$B$8)),Row($A1))

and commit it with Ctrl+Shift+Enter rather than just Enter since it is an array formula. If you then click back on D3 and look at the formula bar, if you entered it properly it will appear as

{=Small(IF($B$2:$B$8>14,ROW($B$2:$B$8)),Row($A1))}

The curly brackets {} are not there. Excel just displays them to indicate that the formula in that cell is being interpreted as an array formula.

so do that do d3 and then drag fill it down to D4

You should see

D3: 5

D4: 7

Now change your formula to

=INDEX($A:$A,SMALL(IF($B$2:$B$8>14,ROW($B$2:$B$8)),ROW($A1)),1) and commit with Ctrl+Shift+Enter again.

We put in $A:$A instead of $A$2:$A$8 since the row returned by the small function is the location of the cell with the value greater than 14 and not which cell in the range B2:B8 is the cell greater than 14.

Now you can select that cell and drag fill down. If you drag fill it down 3 cells, the third cell will return #NUM since you are asking he small function to return the 3rd smallest value and there is no 3rd smallest value.

So if you want to prefill a number of cells and you won't know how many values need to be returned you can use the IFERROR function to suppress the #NUM error.

=IFERROR(INDEX($A:$A,SMALL(IF($B$2:$B$8>14,ROW($B$2:$B$8)),ROW($A1)),1)"") then commit with Ctrl+Shift+Enter and fill down.

if you want to do this on another sheet then say the sheet with the data is named Sheet1

=IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$B$2:$B$8>14,ROW($B$2:$B$8)),ROW($A1)),1)"")

Notice that I don't need the sheet name in row($B$2:$B$8). I am just using that to get the row number. If you highlight that portion of the formula and hit F9 it will evaluate to the array {2;3;4;5;6;7;8}. So it isn't dependent on a sheet name to produce these numbers just like the $A1 is not dependent on a sheet name.

if the sheet name has a space in it, assume say Sheet 1

then

=IFERROR(INDEX('Sheet 1'!$A:$A,SMALL(IF('Sheet 1'!$B$2:$B$8>14,ROW($B$2:$B$8)),ROW($A1)),1)"")

the sheet name will need to be in single quotes.

Perhaps you will involve a varying number of rows to hold your data. YOu can refer to a larger range in the formula such as

=IFERROR(INDEX($A:$A,SMALL(IF($B$2:$B$20>14,ROW($B$2:$B$20)),ROW($A1)),1),"")

Hopefully that provides you the information you need.

--

Regards,

Tom Ogilvy

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | Thanks Tom, very helpful. |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.