Solved Excel function help needed

November 25, 2015 at 01:44:50
Specs: Windows 7
I have two tables as shown below, tables A & B. I would like to use the data in A to create a stacked bar chart, however I would like the information in table A to change depending on what what status is in Table B Project status. If the Project status in B is e.g 5.Production, then I would like all project positions before 5.Production to be at 100% which is 8 (as I have 8 positions) and all project positions after 5.Production to be 0. So the function will basically be showing next steps from 1 upto 8. If we are at step 1 then everything after step 1 should be register 0 as in not yet done, and if we are at step 5 it means steps 1-4 have been fully completed but steps 6-8 are at 0.

Table A
Project status Project positions Total project steps
1.Wachten.......................8................................8
2.In ontwerp.....................8................................8
3.Tergoedkeurin................8................................8
4.Gelanceerd....................8................................8
5.Production.....................0................................8
6.Afgett...........................0................................8
7.Deellevering..................0................................8
8.Geleverd.......................0................................8

Table B
Project # Project status
150565 5.Production

message edited by JoeMuz


See More: Excel function help needed

Report •


#1
November 25, 2015 at 05:46:21
✔ Best Answer
First, a posting tip:

If you click on the blue line at the end of this post you will find the instructions on how to format example data so that it is easier for us to read. In the future, please use that method. Don't forget to use Column letters and Row numbers as shown in the example. Thanks!

As for your question, if you start with this layout, then formula below should work for you:

  
              A                 B                     C 
1     Project status     Project positions   Total project steps
2       1.Wachten               8                     8
3       2.In ontwerp            8                     8
4       3.Tergoedkeurin         8                     8
5       4.Gelanceerd            8                     8
6       5.Production            8                     8
7       6.Afgett                0                     8
8       7.Deellevering          0                     8
9       8.Geleverd              0                     8
10
11      Project #        Project status
12       150565           5.Production

Put this in B2 and drag it down:

=IF(MATCH($B$12,$A$2:$A$9,0)>=MATCH(A2,$A$2:$A$9,0),8,0)

This formula uses the MATCH function to determine the position in the array (A2:A9) of the value in B12. If the position is Greater Than or Equal To the position of the "status" in Column A, then an 8 is returned.

Please note that the MATCH function does not return the Row number of the lookup_value, it returns the position in the lookup_array.

e.g. 5.Production is in Position 5 of the lookup_array (A2:A9) even though it is in Row 6 of the worksheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
November 25, 2015 at 06:15:34
Thank you Derby Dad! The pointers on how to clearly post are handy, I'm still new at this so I'll def take it on board with my next posts.

The MATCH function works well for all Project positions between 1-7 thanks, just 1 slight hitch in the end. When my value in B12 from the example above = 8.Geleverd, then the formula for everything else (1-7) goes to #N/A instead of saying 8. When B12 = 8.Geleverd, all Project positions should also be 8, thus all Project steps complete.

Thanks!


Report •

#3
November 25, 2015 at 09:01:43
You must have done something different because I do not have that problem.

When B12 = 8.Geleverd, all I see is eight 8's in B2:B9.

Are you sure that your lookup_array is correct? Are you sure that the lookup_value is an exact match? (Try copy/paste from your table to B12). One possible cause (the most common) of a #N/A from a lookup function is when the lookup_value is not found in the lookup_array.

You might want to try the Evaluate Formula feature on the Formula ribbon. This will allow you to single step through the formula and see exactly what portion is returning the #N/A error.

Assuming you are using the exact setup I use in Response #1, post the exact formula you are using and I'll test it in my worksheet.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 25, 2015 at 11:23:37
You were right DerbyDad, found the error when I looked carefully at the formula in the cell. All clear & working, thanks again!

Report •

Ask Question