Excel help please...

[tvradict]

I'm creating a new workbook and there are a couple of things I am struggling to do.

First one. I need the first 5 columns of the worksheet to stay visible on the left when scrolling right. I know this can be done but have no idea how.

The second one, probably requires a formula, but I'm not sure how to do it anyway.
To explain, the first 4 columns (A-D) detail the person, this wont change, but the rows may move down the worksheet, the 5th column (E) shows a total which I will come back to in a minute. All the other columns refer to dates. On each of the these dates, the people should have paid us £1. If they dont, then they obviously owe us £1, if they pay more than £1 on any given date, the extra goes to either pay off any outstanding monies owed, or accumulates. I need this total to show me either, how much they owe us, if they are Correct (ie, nothing owed, nothing accumulated) or how much we owe them.

I tried to use the =SUM(F7:BK7) but I think it is too simple a foumala and it doesnt show me the correct amount in the total column.

Anybody have the solutions to these???

TIA
Stuart

[oldbanger]

For the first one, click in cell E2, then go to the top menu bar, select Window, then select Freeze Panes. This will keep columns A to D and row 1 in view at all times.

Edited to add - just noticed it says 1st 5 columns, in which case click in cell F2

Edited by oldbanger on Monday 8th March 00:40

[oldbanger]

as to the 2nd part of your question, what figures are you putting in each cell if the person doesn't pay on a particular date? If you are putting in -1 then the sum function should work, but if you are leaving it blank then yes you'd need a more complex formula

Edited - no I got this wrong, please ignore, it's waay past my bedtime

Edited by oldbanger on Monday 8th March 00:59

[tvradict]

oldbanger said:

For the first one, click in cell E2, then go to the top menu bar, select Window, then select Freeze Panes. This will keep columns A to D and row 1 in view at all times.

Edited to add - just noticed it says 1st 5 columns, in which case click in cell F2

Edited by oldbanger on Monday 8th March 00:40

Thanks. Sorted

[Big Al.]

tvradict said:

First one. I need the first 5 columns of the worksheet to stay visible on the left when scrolling right. I know this can be done but have no idea how.

Anybody have the solutions to these???

TIA
Stuart

Using the drop down from the windows, you can use the freeze panes option. Select the cell in the sixth column and select freeze panes.

If you select the sixth cell under a title, the title will also freeze, so you can read the title regardless of what row you are in.

HTH and makes sense.

It's a bit late now for formulae if I can get it right it'll have me up all night.

I'll leave that for the guru's in the morning.

[tvradict]

oldbanger said:

as to the 2nd part of your question, what figures are you putting in each cell if the person doesn't pay on a particular date? If you are putting in -1 then the sum function should work, but if you are leaving it blank then yes you'd need a more complex formula

If a person doesn't pay, then yes, they get a -£1.

I've been trying to figure out why the SUM function isn't working.

For instance, my Row 7, starting at F currently reads, -£1 -£1 £2 -£1 -£1.

The SUM function in E7 read =SUM(F7:J7) . The answer I get for the above figures is -£2.

Now, you dont need to be a mathematical genius to realise that something is amis. That person should have paid £5 for the 5 days in that month, they only paid £2, which means they owe us £3.

[Plotloss]

So why not record PAID YES and PAID NO using a =COUNTIF

Then multiply the paid no total by £1 which will give the figure owing

[tvradict]

Plotloss said:

So why not record PAID YES and PAID NO using a =COUNTIF

Then multiply the paid no total by £1 which will give the figure owing

I hadnt thought of that, however, I cant do that because some people will pay £2, £5, £10 on one night, then not pay for a few. I need to record the amount paid. One person paid £80 in one night.

[twister]

tvradict said:

For instance, my Row 7, starting at F currently reads, -£1 -£1 £2 -£1 -£1.

The SUM function in E7 read =SUM(F7:J7) . The answer I get for the above figures is -£2.

Which is exactly the answer you'd expect it to give.

tvradict said:

Now, you dont need to be a mathematical genius to realise that something is amis. That person should have paid £5 for the 5 days in that month, they only paid £2, which means they owe us £3.

The problem is that they owe you £(1xN) (where N is the number of days over which the sum is calculated), but you're only debiting them £1 on the days they don't pay, so the more days on which they pay the less days it seems as if they owe you for... In your example here, they owe you for 5 days but have made a payment on one, so the way you're entering the values makes it look as if they only owe you for 4 days, hence the total being -£2 rather than -£3. What you need to be doing is either

a) working out how much they owe you based on the elapsed number of days (the £1xN value above), only entering payments made (i.e. don't enter -£1 on the days they don't pay), taking the sum of all these payments made and subtracting it from the total owed so far.

b) continuing to insert -£1 for each day a payment is not made, but then ALSO inserting -£1 on the days where payment is made - i.e. each payment made is reduced by £1 before entry. This accounts for the £1 they owe you on the days payments are made.

b2) using two rows per customer, or two columns per day - one for debits and one for credits - to keep the -£1's and any payment amounts seperate.


or as others have suggested, getting into Excel functions and conditional calculations.

[Stubby Pete]

twister said:

a) working out how much they owe you based on the elapsed number of days (the £1xN value above), only entering payments made (i.e. don't enter -£1 on the days they don't pay), taking the sum of all these payments made and subtracting it from the total owed so far.

Exactly what I would've suggested, must be the easiest way.

[tvradict]

twister said:

a) working out how much they owe you based on the elapsed number of days (the £1xN value above), only entering payments made (i.e. don't enter -£1 on the days they don't pay), taking the sum of all these payments made and subtracting it from the total owed so far.

That looks like the easiest solution to put into the current worksheet.

The other + point to using that method would be that I can then see how much was taken on any given day.

Thanks for all the help.

[ccr32]

=(SUM(F2:XFD2))-(COUNTA($F$1:$XFD$1))

Pretty sure that this will work providing that your dates which run from cell F1 across to wherever are only populated with dates once that event has taken place (i.e., you wouldn't put the latest date in row 1 until that event has happened/is about to happen).

PM me if you'd like me to send you a mock-up example.

[CobolMan]

One way round it would be to have -£1 for every day they don't pay and £(x-1) for every day they do pay, where x is the amount received.
In your example, that would give you -£1, -£1, £1, -£1, -£1 which, when you use the SUM function would give you -£3. If the total is <£0 then you know they owe you, if it's =£0 then they're all square and if it's >£0 then they're in credit.