Excel 2003 Nested IF error

[cookmd]

Hello again!


I'm running into an issue with my Nested IF function, Is anyone out there able to take a fresh look at my formula? Thanks in advance!


I am trying to calculate one column based on the values in another column. Here is the formula I have so far, but it's throwing an error message.

=IF(E2="MIN/PC",(D2),"",IF(E2="HRS/PC",(CONVERT(D2,"hr","mn")),"",IF(E2="PCS/MIN",(D2*60),"",IF(E2="PCS/HR",(D2/60),""))))

Did I attempt to nest too many functions, or did I make some other rookie mistake?

 

[cookmd]

I corrected the formula with


`=IF(E2="MIN/PC",(D2),IF(E2="HRS/PC",(CONVERT(D2,"hr","mn")),IF(E2="PCS/MIN",((D2)*60),IF(E2="PCS/HR",((D2)/60),""))))'


Just need to correctly convert from unit/time to time/unit now. any ideas?

 

[prasaddn]

Hi CookMD


I am able to understand what your E2 will be, but can you paste few sample data as to what data/format would be in D2 column?


Quick Tip: Try choose() for your situation..


Regards,

Prasad DN

 

[cookmd]

I don't know if CHOOSE is going to be a viable option as one field determines a calculation that is likely to return a result that exceeds 29.


I will try to explain more about my data...


Column B has a number that can be anywhere from 0 to 100.99, if not higher.

Column C gives meaning to column B by clarifying what the number represents.

In this case, column C will be one of 4 values.

MIN/PC (minutes per piece)

HRS/PC (hours per piece)

PCS/MIN (pieces per minute)

PCS/HR (pieces per hour)


In order to be consistent and meet business requirements, all values must be converted to minutes/piece, (MIN/PC).


Other useful information:

The data comes from an ODBC connection to Acess.

The data updates from Access upon opening the spreadsheet.

 

[SirJB7]

Hi, cookmd!


Tell me if I'm wrong, but I think your original formula doesn't perform the right calculation. Let's see.


OriginalUnit ConversionToMin/PC Details

Min/Pc X (original value)

Hr/Pc X*60 (or Convert hr to min)

Pc/Min 1/X (inverse of first case)

Pc/Hr 1/X*60 (mix of both second and third)


So I believe that your formula should be like this:


=SI(B2="Min/Pc";A2;SI(B2="Hr/Pc";A2*60;SI(B2="Pc/Min";1/A2;SI(B2="Pc/Hr";1/A2*60;"Unit error")))) -----> in english: =IF(B2="Min/Pc",A2,IF(B2="Hr/Pc",A2*60,IF(B2="Pc/Min",1/A2,IF(B2="Pc/Hr",1/A2*60,"Unit error"))))


Please check that. You can use this values to perform the test.


Quantity Unit QuantityMin/Pc

100,00 Min/Pc 100,00

1,67 Hr/Pc 100,00

0,01 Pc/Min 100,00

0,60 Pc/Hr 100,00


Regards!

 

[cookmd]

You're correct, the qty / time conversion calculations on my original post were off. Convert still works to convert hours to minutes, but cell-name *60 may be better.


I had been focusing so much on the formula syntax that I didn't convert the last two portions correctly. Thanks!


I finally settled on this formula to meet my needs:

=IF(E2="MIN/PC",(D2),IF(E2="HRS/PC",(D2*60),IF(E2="PCS/MIN",(1/(D2)),IF(E2="PCS/HR",((1/D2)*60),""))))


Thanks Again!

 

[SirJB7]

Hi, cookmd!

Glad that it now runs as wanted.

The main problem wasn't the use of Convert or multiplying by 60, but the last two conversions didn't work properly.

Regards!