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:
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,451
Members
410,612
Latest member
MrACED
Top