Excel question again.

aliasojo

Yes, I've just been mucking about with the sheet and that has just dawned on me.

Will need to give this more thought obviously.

Have you any idea about this q above.......

Ok...that worked, when I deleted each £50 and the amount in C2 went to 0, B2 showed 'pd'.

BUT.... cell A" then showed 0 too and it should be showing £200?

Edit: Looks like inserting that code into cell B2 cancelled out the previous sum code. Can't I have both in the one cell?

aliasojo

gb12345 wrote: »

The problem you've got is that as you delete the amounts from the week cells you are losing how much was actually due so you've no way of telling how much was paid.

Ok, I've added another column and done this....=IF(D2=150,"50","")

so if if £50 is deleted from wk1, it then shows up in the received column.

I now need to find a way to get more than one formula in for the one cell...I need to also have =IF(D2=100, "100","") and =IF(D2=50."150","") etc etc......can I have more than one formula in a single cell?

gb12345

Yes, the IF statement in A2 takes a value from C2 when the money is paid, but by then C2 has 0 in it.

You could add an extra column to the end (money initially owed or something) and manually enter the total amount in there and don't change it. You could then change the C2 in the IF statement in A2 to H2.

gb12345

aliasojo wrote: »

Ok, I've added another column and done this....=IF(D2=150,"50","")

so if if £50 is deleted from wk1, it then shows up in the received column.

I now need to find a way to get more than one formula in for the one cell...I need to also have =IF(D2=100, "100","") and =IF(D2=50."150","") etc etc......can I have more than one formula in a single cell?

That would only work if your payments were always 50.

As to multiple formula, you can nest the IF statements, but it will get very messy with 4 of them.

aliasojo

gb12345 wrote: »

That would only work if your payments were always 50.

As to multiple formula, you can nest the IF statements, but it will get very messy with 4 of them.

The payments are static and wont change.

How would I go about 'nesting' the IF statements?

I did try to search but tbh it's in complicated (to me) terms and I'm not quite getting it yet.

aliasojo

Ah I've done it! Yay!

Thank you for your help with the formulas. I find it easier to start with the answer if you like then fiddle with it to try to understand it, than I do if I start from scratch with nothing.

gb12345

aliasojo wrote: »

The payments are static and wont change.

So there are always 4 payments of £50?

If that is the case there is no need for your addiitonal column just change the formula in A2 so that it says =IF(B2="pd",200,""). Then it will display 200 when everything is paid.

aliasojo

gb12345 wrote: »

So there are always 4 payments of £50?

If that is the case there is no need for your addiitonal column just change the formula in A2 so that it says =IF(B2="pd",200,""). Then it will display 200 when everything is paid.

The payments will always be £50 but sometimes there are 5 weeks in the month so there would be 5 x £50 and not 4 x £50.

Can I do this 'nest' thing again in cell A2 to change the formula to work whether it's £200 or £250?

gb12345

aliasojo wrote: »

The payments will always be £50 but sometimes there are 5 weeks in the month so there would be 5 x £50 and not 4 x £50.

Can I do this 'nest' thing again in cell A2 to change the formula to work whether it's £200 or £250?

Unfortunately not, as there would be no way of knowing whether it should be using £200 or £250. The easiest option would be one I gave earlier

gb12345 wrote: »

You could add an extra column to the end (money initially owed or something) and manually enter the total amount in there and don't change it. You could then change the C2 in the IF statement in A2 to H2.

You would need to put the total in I2 to allow for the 5 week months.

aliasojo

Really appreciate your help, thanks again. I'll go and 'play' with it all again to try understand better and to try out your suggestion.