Nested If

[tubbsy]

Hi,

I have a spreadsheet 4 columns, shown below:

| Col A | Col B | Col C | Col D |
1| A |56-20 | | |
2| H |12-24 | | |

There are multiple rows. Col A can only be an A or an H. Col B displays any combination of numeric values separated by a -, eg 102-3. Col B will change at specific intervals. The formula needs to cope with this.

In cell C1: If A1 = A display 20 (from B1) Else If A1 = H then display 56 (from B1). If there is nothing in B1, C1 = 0.

Also:
In cell D1: If A1 = H display 56 (from B1). If Col A = A display 20 (from B1). If there is nothing present in B1, D1 should show a 0.

Both formulas need to be present in their respective columns where data is present in A and B.

[jtp]

Hi,
Sounds like your looking for the exact same formula in column C and D. Is that correct? If so just set D1 = C1. As for C1, try this:

=IF(AND(A1>0,B1>0),IF(A1 = "A",RIGHT(B1,LEN(B1)-SEARCH("-",B1)),IF(A1 = "H",LEFT(B1,SEARCH("-",B1)-1),0)),0)

HTH
jtp

[tubbsy]

That works exactly as I had hoped.

Thanks very much.

Tubbsy