How to add a fixed value to ANY cell in a chart based on a fixed condition

Hello

i am trying to make a chart to calculate how much $ I have on a daily commuter pass

This is a pass that gets daily deductions and then automatically reloads with a fixed amount
when the pass value drops below a set value

I.E. the card will reload with $30 when the value is <$20

is there a way for excel to automatically add 30 to ANY cell when the value of the cell is <20?

I attached a part of the workbook
the red cells are the ones I need to have 30 added to, and I want that to happen automatically

Thanks for your help

In "straight" Excel, you cannot have both a formula and data in the same cell. It's going to take VBA.

In the attached, you set the fare ($2.75) and some starting amount. The trip information is contained in an excel table. Also is an excel table for holidays. Since both of these are tables, they grow and copy down formulas as they are added automatically.

I have a bunch of helper cells to help show the logic. These can be hidden.

Enter in as many dates in the first column of the table as you want. The week day is the number of the day of the week, Mon =1, Sat = 6 and Sun =7. Weekend is when the week day > 5 (Friday) or if it is a holiday in the green table. This value determines if a fare is used on that day.

From there, "straight excel" does the calculations. This produces negative numbers. Until you click the button. Then the VB code takes over and if the balance from work is less than 20, $30 is added to that balance and goes into the replenish column. This recalculates the rest of the cells.

Under this scenario, you replenish twice in February on the 9th and again on the 22. You have 37.50 left over at the end of the month.