Error(s) in formula = Please Help?

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
I've only worked with Excel for a few months now. There are errors in my formula and would be pleased if an experienced guru could guide me along.

Here's the entire formula ... and below is a breakdown of what I want it to do:

=IF(OR(ISBLANK(B5), ISBLANK(C5)), "", IF(OR(A5="CH",A5="LH",A5="PH",A5="EH"), 0.5*(C5-B5+1)), IF(OR(A5="CO",A5="LV",A5="PD",A5="ED"), C5-B5+1)), IF(OR(A5="HC",A5="HL",A5="HP",A5="HE"), AA5" Hrs"))

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

=IF(OR(ISBLANK(B5), ISBLANK(C5)), "",
SAYS: HEY! If either cells B5 or C5 are blank ... then leave this one blank too!

IF(OR(A5="CH",A5="LH",A5="PH",A5="EH"), 0.5*(C5-B5+1))
SAYS: If codes "CH, LH, PH, or EH" are in cell A5 ... then C5-B5+1 X 0.5

IF(OR(A5="CO",A5="LV",A5="PD",A5="ED"), C5-B5+1)),
SAYS: If codes "CO LV, PD, or ED" are in cell A5 ... then C5-B5+1

IF(OR(A5="HC",A5="HL",A5="HP",A5="HE"), AA5" Hrs"))
SAYS: If codes "HC, HL, HP, HE" are in cell A5 ... then just get the total from cell AA5 and put it here with the word "Hrs" after it.

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

Aside from whatever is preventing it from working ... my code could use some cleaning to make it more effecient ... but it's easier for me to follow this way.

Cheers! :oops:
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

BatmanUK

Board Regular
Joined
Jul 2, 2006
Messages
185
You appear to be closing off some of the brackets a bit too early. Also, you need to use & to append the text Hrs to cell the value in AA5. You don't define what you want to put in the cell if none of the conditions are true, so i've used XXXX.

=IF(OR(ISBLANK(B5), ISBLANK(C5)),"", IF(OR(A5="CH",A5="LH",A5="PH",A5="EH"), 0.5*(C5-B5+1),IF(OR(A5="CO",A5="LV",A5="PD",A5="ED"), C5-B5+1, IF(OR(A5="HC",A5="HL",A5="HP",A5="HE"), AA5&" Hrs","XXXX"))))

Also, you might need to be aware that ISBLANK will not return TRUE if the empty cell is the result of a formula returning "". If this is a possibility use LEN(Cell)=0 to measure the length of the value in the cell.

Hope this helps.
 

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
Thanks! I do believe you've done it!

Thank you for your help and generosity!

Kevin.
 

Forum statistics

Threads
1,141,011
Messages
5,703,728
Members
421,312
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top