Need help with if(and formula.

[guleman]

Hi there,

i have been trying to create a formula lately but with no sucess. What is has to do is in cell C6 it should bring up text given in cell a100 or a101.
To explain the spreadsheet, it basically shows the times for each phase. If the actual start/finish dates are entered higher than planned start/finished, it should make cell C6 pick A100(Late) if not cell A101 (on schedule). PROBLEM: if the time in next actual start phase (Actual start for BOM Approved F12) keeps exceeding the planned E12, it should keep A100, if not, take A101 and so on until the last time in H13.

It is hard to explain what i acutally need as english is not my mother language but any help would be apreciated. Is there any other formula i could use, that would look at the time entered, if its FALSE(formula) and next one is TRUE and the next after FALSE again, it should change the cell C6 according if its false or true.
Picture added as attachment, let me know if anything else is needed.

[ben_hensel]

I think this is what you need, but I cannot be sure with just an image instead of an example workbook. Please test, and if there is wrong behavior, post example spreadsheet.

{=IF(AND(E7:E13<F7:F13,F7:F13<G7:G13,G7:G13<H7:H13),A100, A101)}
That is an array formula, so you must complete with CTRL+SHIFT+ENTER, not just the ENTER key.

Alternatively:
{=IF(AND(D7:D13=A100), A100, A101)}

Might also deliver what you want?

[guleman]

It did not work, probably because you could not get your hands on it. Ill attach a test file. In it, you can see, that in cell d11 i have entered time that is meets the time in e11, but the cell c6 stays a100(late) What i want to achieve is the cell c6 changing to either a100 or a101 if the entered actual times exceeds or meets the planned times in cells e7:e13; g7:g13. So adding a time in actual time cells should change the c6 either to a100 or a101

[ben_hensel]

OK! You have some problems with the underlying logic.

IF( AND(H7 > G7, F7 > E7), "Late", six_more-IFs(check_down) )

If that very first AND delivers TRUE, then it doesn't even go on to check the remainer of the IFs. That carries through with the nesting: Once row 9 returns a TRUE in the third nested IF, it won't even check rows 10 - 13 because those are in the ELSE term that is not checked because it already returned a TRUE.

Anyway,
If you just want to know whether the MOST RECENT ITEM is "On Schedule" or "Late",
then:

C6 =INDEX(C7:C13, MAX(MATCH(1000000000,H7:H13,1), MATCH(1000000000,F7:F13,1)))

[guleman]

It does work only with column F, when the column H is up to date, it does not change cell C6 back to ''On schedule'',. whci h i think is because it takes the status for current row. So if the first time is late, but second one isnt, it still keeps the status ''late''. That could be because of wrong forumula for detecting the status which is - =IF((F7>E7);$A$100;IF((H7>G7);$A$100;$A$101)), might be the same problem as you explained earlier, that if the first option is met, it does not look at the second. What to use instead?