Nested if

[rry2k]

Hi,

Can someone please give me a formula for a nested if? I am counting letters in a column like y,n,m. I can do the simple if but not the nested. =if(a1="y",1,0) this works but when I try to nest or use an or it bombs. Also how can I pull in totals from different spreadsheets? i.e. data and some monthly totals are in a sheet called monthly and I would like to pull these into a new sheet called yearly maybe in a different folder.

Thanks alot for the help..
Russ

 

[DrBowes]

This below point you int the right direction for syntax, etc. Workbook names go in [], sheet names are followed by an exclamation point. This is all done automatically if you select the required cells on different sheets when writing the formula. If you then close linked workbooks the formula is automaticaly converted to the full path to the workbook. If you change any sheet or workbook names the formula automaticaly updates coz Excel is good like that!

=IF([Book1]Sheet1!$C$4=3,4,IF([Book1]Sheet1!$D$5=6,IF('C:\Documents and Settings\Julia\My Documents\My Pictures\Robert\[Robert permutations.xls]Connections'!$B$15="T",1,2),8))

 

[KenWright]

=IF(A1="a",222,IF(A1="b",333,IF(A1="c",444,555)))

If A1 = a then you get 222 etc, but if value not found you will get 555.

=CHOOSE(MATCH(A1,{"a","b","c"},0),222,333,444)

Assuming your data will be one of the options, then the MATCH will return a 1,2,3 depending on which one it matches, and will feed the CHOOSE function the appropriate argument to return the 222,333 or 444.

=VLOOKUP(A1,{"a",222;"b",333;"c",444},2,0)

{"a",222;"b",333;"c",444} represents an array that is the same as having a column of letters and corresponding values, eg

a 222
b 333
c 444

Will lookup the value in A1 in the leftmost column and then return the corresponding value from the 2nd Column of data. If used with a range of data on the spreadsheet, this would take the form:-

=VLOOKUP(A1,B5:C7,2,0)

B5:B7 would hold the a, b, c from above, and C5:C7 would hold the 222,333,444.

The ,0 in each formula means that it must find an exact match for it to return anything, as there are times when closest match will do, eg when looking up a number that falls within a band.

Regards
Ken..............






Regards
Ken..............



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[rry2k]

Excellent thanks for the help to you both.