Excel Help Needed - IF Statement

timberflake

Hi guys, I need help with an Excel If statement!

I have a set of data, in one column I have the date received, in the next I have current date "=today()", and in the 3rd column I have days elapsed "=DATEDIF(B4,C4,"d")".

Now, what I want to do is add a "RAG Status" column with automatically updates in response to how many days have elapsed. So, if 0 days have elapsed I want the word GREEN to appear, if 1 day has elapsed I want the word AMBER, and if 2 OR MORE days have elapsed I want the word RED to appear.

Can anyone advise the best way to do this??

EDIT: It's actually more complicated than that! I also need to incorporate a status so regardless of the days elapsed if the status says "Complete" the RAG status says GREEN.

stevemcol

IF(d4=0,"green",IF(d4=1,"amber",IF(d4>=2,"red","")))

or use similar formula with conditional format to actually colour the cell.

stevemcol

is the 'complete' status in another cell?

stevemcol

=IF(A1="complete","green",IF(D4=0,"green",IF(D4=1,"amber",IF(D4>=2,"red","") )))

Assuming complete is in A1 which is won't be, just change.

timberflake

stevemcol wrote: »

is the 'complete' status in another cell?

Yes, the complete status is from a drop down in CELL H4.

So if it says complete in H4 I want the RAG Status to always be GREEN, alternatively the RAG status will be determined by the days elapsed.

timberflake

Sir, you are a legend! Thanks for your help!

timberflake

I have another question! I'm trying to create a summary sheet which tracks the status of all types of requests received and their RAG Status.

So, I have a request called "Agency Creation", and have inserted the following to count all requests received:
=COUNTIF('Work Log'!G4:G1000,"Agency Creation")

But I also want to keep track of the the RAG status of the Agency Creation requests. I therefore entered the following formula:

=COUNTIF('Work Log'!G4:G1000,"Agency Creation") * COUNTIF('Work Log'!J4:J1000,"GREEN")

However, this doesn't quite work as it counts all Agency Creation requests regardless of their RAG status. Any idea what I'm doing wrong?

stevemcol

that equation will multiply the instances of 'agency creation' by the number of 'greens'. Is that what you intended?

i think what you want is a countif where both criteria are satisfied. there are a few ways of doing it. you might create another column, containing the equation
=IF(and(a1="agency creation",b1="green"), true, false)
(Tweak the cell references to suit your worksheet)

then create a countif for instances of true.

There are other ways.