Can anyone help me with an excel formula.

basically I have a list of months with the number of working days in the cells next to them, I want to be able to enter the month and have the sheet workout the working days - I've used this formula which works ok but I don't know how to add the additional elements for the other 11 months ?

=IF(C3=0,"",INDEX(P4 : Q15,MATCH("Jan",P4 : P15,0),2))

INDEX is where the list is located and MATCH uses that to provide the result, but it's a complete formula - where would I add the req for Feb,Mar etc or indeed can I ???

Any help gratefully received.

Hi

I can't really envisage what you're trying to achieve from your formula (sorry). By INDEX, I assume you've made up a table and are locating (in excel the nearest) matching answer?

However, if you have Jan, Feb etc as fixed, and the no. of days next door fixed, then couldn't you run the formula off days, i.e. this reduces it to 4 options, meaning only 4 nesting Ifs), i.e. IF 31, 30, 28, or 29 for leap years.

Is that possible as not sure what the latter part is trying to do?

Mo

Yes I have a two column table - 1st column months and 2nd for working days -- this is not the number of days of the month, but the number of days that we will work that month.

I'm trying to be able the month in a cell and have excel retrieve the correct working days and display that in another cell.

Multi-tier IF statements - one of the more fiddly aspects of excel.

What you need to do is replace the value for if the condition is not met with another if statement for each month inserting the original condition not met value at the end of December.

Hope that helps.

EDIT:

=IF(C14="b","J",IF(C14="a","L",IF(C14="c","L","K")))

This is a multi-tier IF statement I use on an assessment resource on my website. Hopefully it explains it a little more clearly.

Oh, if anyone thinks it looks odd the cell it's in is formatted in Wingdings font.

Whenever I've used tables, I've used the Lookup (Vertical or Horizontal) which returns the nearest value in the table to that for which you are looking (i.e. no nesting required).

Unfortunately, I've not found a way in Excel (though others might have) to project the answer to another cell. (I.e. to emulate the Get feaure of Lotus 123). IOW, you might need to retrieve the answer in a cell, and get the desired end-cell to do a "plus A2(whatever)"

Tables IMO are better set up numerically, I.e. Jan-Dec = 1-12 (which can be set up next door for reference, just refer to the numerical column in your Lookup formula) which simplifies things enormously.

Are we still on the same wavelength?

Mo

Whenever I've used tables' date=' I've used the Lookup (Vertical or Horizontal) which returns the [b']nearest[/b] value in the table to that for which you are looking (i.e. no nesting required).

Unfortunately, I've not found a way in Excel (though others might have) to project the answer to another cell. (I.e. to emulate the Get feaure of Lotus 123). IOW, you might need to retrieve the answer in a cell, and get the desired end-cell to do a "plus A2(whatever)"

Tables IMO are better set up numerically, I.e. Jan-Dec = 1-12 (which can be set up next door for reference, just refer to the numerical column in your Lookup formula) which simplifies things enormously.

Are we still on the same wavelength?

Mo

That sounds like a plan to me I'll give it a whirl

In the cell where you want the number of working days type :

=VLOOKUP(A1,Sheet2!$A$1:$B$12,2,FALSE)

Where Sheet2 cells A1 to B12 has the list of months and corresponding working days in column B. Copy formula down the spreadsheet and bob's your uncle (hopefully)

The FALSE means get an exact match and A1 is the cell value (ie the month) that you want to look up

Corrrrr... all this takes me back to my days as a Data Analyst in the NHS, 5 years ago I'd have been able to sort the correct formula in no time, haven't got a clue now - Amazing how quickly you "de-skill" !!!!

In the cell where you want the number of working days type :

=VLOOKUP(A1' date='Sheet2!$A$1:$B$12,2,FALSE)

Where Sheet2 cells A1 to B12 has the list of months and corresponding working days in column B. Copy formula down the spreadsheet and bob's your uncle (hopefully)

The FALSE means get an exact match and A1 is the cell value (ie the month) that you want to look up[/quote']

:orb_not_w

WOW! That's the one - exactly what I was looking for THANKS!

briskoda to the rescue AGAIN!!

You're welcome

In the cell where you want the number of working days type :

=VLOOKUP(A1' date='Sheet2!$A$1:$B$12,2,FALSE)

Where Sheet2 cells A1 to B12 has the list of months and corresponding working days in column B. Copy formula down the spreadsheet and bob's your uncle (hopefully)

The FALSE means get an exact match and A1 is the cell value (ie the month) that you want to look up[/quote']

FAO Jane: Thanks from me also, re that FALSE.

Mo