You are here:

Excel/Excel complicated formula condition

Advertisement


Question
I am trying to created a summary result of other variables in my worksheet.
There are 5 variables (A, B, C, D, E) and 2 sheets (P1 and P2) that are in play.

Summary is listed vertically.

I can get this formula right.
=IF(P1!E9>0,P1!B9,IF(P1!E10>0,P1!B10,IF(P!!E11>0,P1!B11, IF(P1!E12>0,P1!B12,IF(P1!E13>0,P1!B13,"")))))
To give me A in the top cell. I than make the above formula shorter (Drop the first P1!E9 part) to get B in the next cell below and so on to get all the way to E.
Works great as long as there is always quantities in all variables (A-E) I get a vertical list of
A
B
C
D
E

But if A is not >0 I get
B
B
C
D
E
And if C is not >0 I get
B
B
D
D
E

How do I make it for the example of no A or C have the result of
B
D
E
only?

What I need is
1st Cell: Formula works
2nd cell: if 1st cell is blank or has B then do not show B
3rd cell: if 1st or 2nd cell is blank or has C then do not show C
4th cell: if 1st, 2nd or 3rd cell is blank or has D then do not show D
5th cell: if 1st, 2nd, 3rd or 4th cell is blank or has E then do not show E

Answer
I'm pretty sure this ARRAY formula will do what you want:

=INDEX($B$9:$B$13, SMALL(IF($P$9:$P$13>0, ROW($P$9:$P$13)-8, ""), ROWS($A$1:$A1)))

Enter that formula into the first cell and confirm it by pressing CTRL-SHIFT-ENTER, and the "B" should appear if that's the first row with a value > 0.  You will know the array is active in that cell because you can see curly braces { } appear around your formula.

If you get a #VALUE error, you probably don't see the braces { } either, so try entering the formula again.  CTRL-SHIFT-ENTER feels weird until you're used to doing it instead of just ENTER.

Once the first value appears, copy that cell down.  You'll get #NUM errors when you run out of results.  You can suppress the NUM errors by adding an IFERROR() around the formula:

=IFERROR(INDEX($B$9:$B$13, SMALL(IF($P$9:$P$13>0, ROW($P$9:$P$13)-8, ""), ROWS($A$1:$A1))), "")
About Excel
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

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.