Hello,
I'm making an attendance spreadsheet for my local cycling club to record payments. The spreadsheet uses text to mark attendance (o=paid, x=unpaid, f=free session). A SUM COUNTIF function is used to 'assign' numerical values to these (E9:N9). The value can change each session due to what facilities are used.
The problem however is that some members like to pay in advance with 'credit'. B12 is credit input value, E12:N12 is attendance, C12 is credit function: =B12-(SUM(COUNTIF(E12:XFD12,{"x","o","f"})*{0,5,0}))
What I am trying to achieve is to have a credit input (B12) which then automatically updates all unpaid sessions ('x') to paid sessions ('o'). Once, the credit value (C12) reaches 0, I want it to 'reset' but leave the paid sessions as paid, so that a new Credit input can be given and the function to occur once again but now with new values.
I have partially got it working; but it only works if members pay solely with credit - which they don't. The credit value continues into negative values. I tried using the MAX function to prevent any value less than 0 but the calculations continued to treat it as a negative value anyway.
The ideal is to it so that the credit function (C12) continues to 0 and then 'resets' (allowing credit input figure to be removed and a new one added) but remembers that the values were paid via credit).
My questions:
1. Is there a way to make the function reset when all the credit is used up?
2. The function also needs to use variable numerical values for each entry/ session. The cost of each session is E10:N10. Can this be done using a COUNTIF?
3. How to prevent credit from going below zero?
4. Is there a way to have the credit input (B12) deleted once its used in the function (C12)?
Apologies if i missed any important information (this is my first post).
Thank you,
Bookmarks