Hi All,
I was wondering if anyone would be able to point out what I doing wrongly as i have been struggling with this formula for a couple of hours.
Synopsis
Column 1 (Event date) = 13/06/24, column 2 (Release Date) = 10/05/24, column 3 (Onsale Date) = 10/12/23, column 4 (total No of Rooms) = 100, Column 5 (Event type) = A, Column 6 (No of sales to date) = 20, Column 7 = current date, I want an excel formula that says, based on today's date if the event type is A and the no of sales to date isn't at least 69% of the total number of rooms 24hours after the onsale date, fill column 7 with text "high risk" with font colour red, if between 70% to 99% fill column 7 with text "low risk" with font colour green, if 100% fill column 7 with text "great job" with font colour blue however if the event type is B and the no of sales to date is 49% or less of the total number of rooms 3days after the onsale date, fill column 7 with text "high risk" with font colour red, if the no of sales to date is between 50% and 99% of the total no of rooms, fill column 7 with text "low risk" with font colour green, if the no of sales to date is 100% of the total no of rooms, fill column 7 with text "great job" with font colour blue.
Formula (that keeps generating error)
=IF(OR(AND(E2="A",F2<0.69,G2-I2>1),AND(E2="B",F2<=0.49,G2-I2>3)),"High Risk", IF(AND(E2="A",(F2>=0.69,F2<=0.99)),"Low Risk", IF(AND(E2="A",F2=1),"Great Job", IF(AND(E2="B",(F2>=0.50,F2<=0.99)),"Low Risk", IF(AND(E2="B",F2=1),"Great Job")))))
I'd appreciate if anyone is able to tell me what I am doing wrong. Thanks a lot
