More IF statements

Ian_A85

New Member
Joined
Nov 26, 2010
Messages
21
Hey all,

I have a problem, i need to put some if statements in a ref cell, but i have 10, and can only put 7, help!!

i have:

=IF(D3="SICK",C3,IF(D3="HOLS (all day)",C3,IF(D3="FLEX (all day)",C3,IF(D3="HOLS (am)",C3/2,IF(D3="HOLS (pm)",C3/2,IF(D3="FLEX (am)",C3/2,IF(D3="FLEX (pm)",C3/2," ")))))))

I need to get:
Sick
Hols (all day)
Hols (am)
Hols (pm)
Flex (all day)
Flex (am)
Flex (pm)
Other (all day)
Other (am)
Other (pm)

the cell it refs (C32) counts the hours, so for a all day its counts the whole cell, but for a am or pm it counts then divides by 2, so thats quite essential too!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Try;

=IF(OR(D3="SICK",D3="HOLS (all day)",D3="FLEX (all day)"),C3,IF(OR(D3="HOLS (am)",D3="HOLS (pm)",D3="FLEX (am)",D3="FLEX (pm)"),C3/2," "))
 
Upvote 0
A few questions...

Is C32 always the cell to be referenced?

Is the referenced cell to be divided by two if "am" or "pm" and then taken as a whole number in any other case?

Will that allow me to then put the addtional ones i cannot put in, as it max's out at 7?

What are these "Additional ones"?



If the only time you would divide by two would be if the cell has "am" or "pm" in it, the following might work.

Code:
=IF(OR(IFERROR(FIND("am",D3),FALSE),IFERROR(FIND("pm",D3),FALSE)),C32/2,C32)
 
Upvote 0
I need to get the following into a single if statement.
Sick
Hols (all day)
Hols (am)
Hols (pm)
Flex (all day)
Flex (am)
Flex (pm)
Other (all day)
Other (am)
Other (pm)

The statement i currently have is:
=IF(D3="SICK",C3,IF(D3="HOLS (all day)",C3,IF(D3="FLEX (all day)",C3,IF(D3="HOLS (am)",C3/2,IF(D3="HOLS (pm)",C3/2,IF(D3="FLEX (am)",C3/2,IF(D3="FLEX (pm)",C3/2," ")))))))

my problem is the list of if is need to check, above, is 10, i can only use 7
 
Upvote 0
did you try the equastion i posted?

It looks at cell D3 (as in your example) to see if "am" or "pm" is anywhere in the text. If it is then it returns a result of c32/2, otherwise it returns c32.
 
Upvote 0
Try:

=IF(OR(D3="SICK",D3="HOLS (all day)",D3="FLEX (all day)"),C3,IF(OR(D3="HOLS (am)",D3="HOLS (pm)",D3="FLEX (am)",D3="FLEX (pm)"),C3/2," "))


Jesse
 
Upvote 0
It appears i'm crosseyed today and c3/2 looked like c32 and the rest of my brain made false assumptions from that point on....the equasion i posted is corrected below...

=IF(OR(IFERROR(FIND("am",D3),FALSE),IFERROR(FIND("pm",D3),FALSE)),C3/2,C3)
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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
Back
Top