This sounds conceptually similar to some formatting using custom formulas that @jpfieber introduced for his Transactions sheet here. I ended up implementing the same formatting for my sheet. The formulas I used are below.
- Cell range: $A2:$S (all columns starting with row 2)
- Transfer: =vlookup($D2,indirect(“Categories!$A$2:$C”),3,0)=“Transfer”
- Positive transaction: =$E2>0
- Negative transaction: =$E2<0
I believe you could leverage custom formulas in the conditional formatting rules to accomplish what you’re looking for. Assuming you want to apply the formatting to each Actual column on the sheet, you can use Actual / Budget in the custom formula to get your overspend ratio.
I tested out the following on a sample sheet and added the bottom three rules for the overall Actual column. The formulas will compare the overspend ratio $C7/$B7 to your specified breakpoints (>=1.4, >=1.2, >1) and only apply the formatting to category rows with a type of Expense. Keep in mind that conditional formatting rules are applied in order and the first matching rule will be used.
- Cell range: $C7:$C (Google Sheets will expand this to your actual final row)
- 40% overspend: =AND($C7/$B7>=1.4,vlookup($A7,indirect(“Categories!$A$2:$C”),3,0)=“Expense”)
- 20% overspend: =AND($C7/$B7>=1.2,vlookup($A7,indirect(“Categories!$A$2:$C”),3,0)=“Expense”)
- Up to 20% overspend: =AND($C7/$B7>1,vlookup($A7,indirect(“Categories!$A$2:$C”),3,0)=“Expense”)
And here are the results:
I think this should give you a good starting place to add the formatting you’re looking for, but if you have any other questions I can try to answer them as best I can.