Nested IF AND Formula

[Kmac]

I need a formula that says if there is a number in H15 and C15 then IF(D15<C15,D15+1,D15) if not type an X. this is the formula i am using, and it is not working. i am not getting any errors. but when there is a number in H15, and not a number in C15 i am not getting an X.


=IF(H15>0,AND(C15>0)*IF(D15<C15,D15+1,D15)-C15,"X")

[ratcat]

G'day

Try this

=IF(AND(ISNUMBER(H15),ISNUMBER(C15)),IF(D15<C15,D15+1,D15-C15),"X")

[Kmac]

thanks.

that is giving me X's for everything, and is no longer doing the subtraction.

[simjambra]

=IF(AND(C15="",H15=""),"x",IF(D15<C15,D15+1,D15))

If you want an x should either cell be empty, use OR instead of AND.

[Kmac]

That works, but when H15 and C15 are empty is displays an X. I only need an X when there is something in H15, but not in C15. also the subtraction is wrong. i have attached a sample. see worksheet 63

[Palmetto]

EDIT: delete formula - incorrect, posted before OP workbook made available

[Kmac]

that is not working either

[DonkeyOte]

Kmac, some of the posts appear to contradict moreover it's not really clear from your initial formula as to what the underlying aim of the calculation is ?

Perhaps simplest to outline the expected result for the first cell in your file - ie does the "0" stored as text in H constitute a number or not ? Are you looking to simply calculate the shift duration where appropriate ? ie MOD(Dn-Cn,1)

[Kmac]

The purpose of the formula is to do two things.

1. calculate the duration of the shift

2. if they did not work when they were scheduled place an X where the hours should be.

i have attached a sample with my desired results.

[DonkeyOte]

Perhaps then:

=IF(AND(SUM(H3:I3),SUM(C3:D3)=0),"X",MOD(D3-C3,1))

above assumes also that where they work but are not scheduled hours are still to be calculated.

[Rebuild8]

hi Don,
How is it that the MOD function returns 9:00? If it was divisor is one, wouldn't the remainder always be 0?

[DonkeyOte]

In XL Time is always Decimal value: 06:00 being 0.25, 12:00 being 0.5, 18:00 being 0.75 etc

Assume end time of 18:00 and start time of 06:00

MOD(0.75-0.25,1) -> MOD(0.5,1) -> 0.5 ie 12:00 hours

Assume start time of noon and end time of 6 am

MOD(0.25-0.5,1) -> MOD(-0.25,1) -> 0.75 ie 18:00 hours

For shift durations MOD is a convenient method for calculation IMO.

[Kmac]

=IF(AND(SUM(H3:I3),SUM(C3:D3)=0),"X",MOD(D3-C3,1))

.

That formula is giving me a FALSE when they work and are not scheduled. I am also getting An X for when they work and are scheduled, instead of duration. As well as an X when they do not work, but are scheduled.

i have attached a sample to show you what is is doing.

[DonkeyOte]

Something odd going on there I'm afraid.

Copy the below and paste over the existing formula in E15:

=IF(AND(SUM(H15:I15),SUM(C15:D15)=0),"X",MOD(D15-C15,1))

(if you don't want the 0's to appear you could apply a Custom Format of: h:mm;;;@ to the range E15:E28)

[Kmac]

Something odd going on there I'm afraid.
=IF(AND(SUM(H15:I15),SUM(C15:D15)=0),"X",MOD(D15-C15,1))
[/I]

thats how i changed it, to the correct cell numbers but the same thing is still happening.

[DonkeyOte]

Re-entering the formula calculated correctly for me I'm afraid.

That said I've not seen the issue you're experiencing in your upload before which leads me to believe your file has in some way corrupted.
(if you use the Evaluate formula on the original formula you will find it's doing something very odd indeed...)

Edit:

on an aside - I've not the time to go through this in detail but you have Iteration enabled - are you using Circular references ?

[Kmac]

I think i was, but then i got rid of them. i have turned it off. same problem still.

how do i go about fixing it if it is corrupted?