IF Statement with multiple cells & parameters

[Beardybrady]

Hello, I am creating a spreadsheet for work and I need to show risk against cost, I cannot for life of me think where to start.

I have one Cell that displays percentage spend and one cell that displays the financial quarter.

What I want to display is as the percentage goes through certain thresholds it will turn colour however it needs to remain the same colour as it will be under budget. I wont need to change colour unless it pass the threshold on a certain Quarter.

For example. if the cost is between 25% & 50% in quarter 2 this is fine as its what's expected, if its below 25% then realistically needs to be highlighted as we should have some invoice.

If we are still below 50% spent in quarter 3 and 4 then this is concerning as we are massively underspent, on the otherhand if we are over 75% in quarter 3 then this needs raising as there could be an overspend.

There are so many parameters to this its blowing my mind. I have set up conditional formatting to represent the % boundaries but I feel this is to simple

Can anyone recommend

 

[ETAF]

conditioanl formatting would do it for you
But where or how will excel know what quarter you are in, the budget and spend

maybe even a table to lookup threshold

Q1 - 25% amount - then anything upto 25 % is OK, or the amount
Q2 - 50%
etc

then excel can look that up

but maybe show us a sample spreadsheet and what answers you would like and why

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

i have made a sample - and shown possible formula

 

[Beardybrady]

Thank you for the response, so I used the simple =today() to give me todays date, I then changed the formatting to just display mmm-YY,

I then used ="Q"&CHOOSE(MONTH($H$2),4,4,4,1,1,1,2,2,2,3,3,3), to work out the Quarters for our financial year.

The cost risk is based on budget/ expenditure to give the percentage and as mentioned above I have conditional formatting to currently display the colours

I am trying to write a formula to input into the conditional formatting so mate the conditional formatting so the percentages match year if you know what i mean

so Q1 up to 25
Q2 25 - 50
Q3 50 - 75
Q4 75 and up

However as mentioned, its ok to be underspent up until a point but if we havent got the same % spend as quarter spend then there could be issues.

So of Q3 displayed 60 % spend thats as you would expect but it Q3 displayed 40% then you would be concerned and similar Q3 and at 90% you would be concerned

I really do hope this helps i will look more in depth at your formula now

Jun-23	Financial Quarter	Q1
Budget	Expenditure	Remaining	COST RISK
£ 842,100.00	£ 12,000.00	£ 830,100.00	1%
£ 842,100.00	£ 44,474.17	£ 797,625.83	5%
£ 84,210.00	£ 6,959.67	£ 77,250.33	8%
£ 112,280.00	£ 1,863.00	£ 110,417.00	2%
£ 378,945.00	£ 15,059.68	£ 363,885.32	4%
£ 224,560.00	£ -	£ 224,560.00
£ 112,280.00	£ -	£ 112,280.00
£ 70,175.00	£ -	£ 70,175.00
£ 224,560.00	£ 33,081.67	£ 191,478.33	15%
£ 56,140.00	£ -	£ 56,140.00
£ 2,947,350.00	£ 113,438.19	£ 2,833,911.81	4%

 

[ETAF]

how do you work out the cost risk - could you use that ???

 

[bosco_yip]

1] Please refer to your fiscal quarter formula :

="Q"&CHOOSE(MONTH($H$2),4,4,4,1,1,1,2,2,2,3,3,3)

2] It can be shorter by :

="Q"&QUOTIENT(MOD(MONTH(H2)-4,12),3)+1

or

="Q"&MID(444111222333,MONTH(H2),1)

 

[Beardybrady]

Thanks Bosco, I think i may have figured it out but I dont know how to combine the whole formula without getting a error

=IF(AND(K5<25%,J3="Q1"),"MINOR RISK",""),IF(AND(K5>25%,K5<50%,J3="Q2"),"MINOR RISK",""),IF(AND(K5>50%,K5<75%,J3="Q3"),"MINOR RISK",""),IF(AND(K5>75%,J3="Q4"),"MINOR RISK",""))))

 

[bosco_yip]

Thanks Bosco, I think i may have figured it out but I dont know how to combine the whole formula without getting a error

=IF(AND(K5<25%,J3="Q1"),"MINOR RISK",""),IF(AND(K5>25%,K5<50%,J3="Q2"),"MINOR RISK",""),IF(AND(K5>50%,K5<75%,J3="Q3"),"MINOR RISK",""),IF(AND(K5>75%,J3="Q4"),"MINOR RISK",""))))

Something like this:

=IF(AND(K5<25%,J3="Q1"),"MINOR RISK",IF(AND(K5>25%,K5<50%,J3="Q2"),"MINOR RISK",IF(AND(K5>50%,K5<75%,J3="Q3"),"MINOR RISK",IF(AND(K5>75%,J3="Q4"),"MINOR RISK",""))))