excel geeks in here plz

HAz · 24 Aug 2011 at 11:20

Hi guys

Taken on the work lotto and decided to put it all onto a spread sheet to make it easier. I'm no expert on Excel but im ok at it i guess.

Basically what i want to do is a sum of some sort that if jon french at the top pays me £10 it will split that along the cells automatically.

As you can see jon french is paid his £1.50 up till August the 2nd but if he gave me £10 hes now paid up till sept 13th + £1.00.

So what would be nice is if i put £10 next to his name then the blank cells fill up into £1.50 chunks up till the 13th and leave an amount of £1.00 in the 20th cell.

I can upload the sheet if people want a play around with it.

cheers

https://rapidshare.com/files/2101022660/lotto.xlsx

A.N.Other · 24 Aug 2011 at 11:27

Might be easier if you upload it, but should be pretty simple.

+ it's Excel

Participant · 24 Aug 2011 at 11:32

'Jason Tozer', heh

HAz · 24 Aug 2011 at 11:33

here is link to file lol, think it should work

https://rapidshare.com/files/2101022660/lotto.xlsx

ShadowMan · 24 Aug 2011 at 11:43

As they all pay the same amount why not just use a marker to denote they have paid the weeks requirement. Then when they pay you can tick the right number of boxes and put the leftover amount next to their name?

Personally I think you are trying to overcomplicate it.

Viper² · 24 Aug 2011 at 11:48

I'm no expert but fill the weekly cells with =IF(BE5>1.5,"£1.50",""), then =IF(BE5>3,"£1.50",""), and so on..?

A.N.Other · 24 Aug 2011 at 12:02

Ta daa.

http://www.freefilehosting.net/lotto2_1

Never used that upload service before (first google hit), so let me know if it doesn't work.

// New link - if you downloaded the previous one, it didn't have the formulas in it.

mp3duck · 24 Aug 2011 at 12:17

Are you playing Euro Millions?
Because that is now £2.00 per draw!

HAz · 24 Aug 2011 at 12:27

A.N.Other said:
Ta daa.

http://www.freefilehosting.net/lotto2_1

Never used that upload service before (first google hit), so let me know if it doesn't work.

// New link - if you downloaded the previous one, it didn't have the formulas in it.

Hi

Thanks for that, I was wondering weather it was possible to show me who owes what up to a certain date?

As you can see jason tozer is £-11 as of the date of august the 23rd, would it be possible for it to show how in debt a person was from todays date?

If you could do that, that would be amazing.

Thanks for your help so far.

alexthecheese · 24 Aug 2011 at 12:32

Would you have some kind of value that increments as time goes on? Have a start date of paying, show how many times there....

Oh I can't be bothered.

A.N.Other · 24 Aug 2011 at 13:45

Here you go then. EVERYTHING autocalculates from the PAID column, so don't go trying to alter other columns yourself

http://www.freefilehosting.net/lottov3

HAz · 24 Aug 2011 at 15:00

A.N.Other said:
Here you go then. EVERYTHING autocalculates from the PAID column, so don't go trying to alter other columns yourself

http://www.freefilehosting.net/lottov3

That is just what I wanted!

Thank you so much

A.N.Other · 24 Aug 2011 at 15:07

*Blush*

Viper² · 24 Aug 2011 at 15:16

Sam Samsome, is that a real name?

Pug · 24 Aug 2011 at 15:22

that's some pretty amazing excel, very cool 8)

A.N.Other · 24 Aug 2011 at 15:43

Pug said:
that's some pretty amazing excel, very cool 8)

The formulae would be a lot simpler if it weren't for ISO 8601 dates.

Vonhelmet · 24 Aug 2011 at 15:47

Meh, call me when you're written 1000 lines of VB in the background :eek:

A.N.Other · 24 Aug 2011 at 15:50

Wouldn't touch VB with a barge pole. If that level of jiggery pokery is required then csv -> python -> csv.

Participant · 24 Aug 2011 at 16:02

I like recording macros then pretending I know VB.