Author Topic: Excel help  (Read 59482 times)

Offline kavah

  • the Blacksmith. Definitely NOT from Blackpool!
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 19,634
  • We all Live in a Red and White Kop
Re: The Microsoft Excel Help Thread
« Reply #80 on: November 6, 2014, 02:27:10 pm »
My birthplace, never felt the need to go back though.  Are you a native, or a visitor?

just a visitor, been here for a year, probably here for another 2 or 3, trying to learn how to use excel amongst other things :D

1972 Saigon must have been rather different to 2014 HCMC!
« Last Edit: November 6, 2014, 02:30:18 pm by kavah »

Online PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: The Microsoft Excel Help Thread
« Reply #81 on: November 6, 2014, 02:41:22 pm »
Can't say I know either of them.

Are you there for 'work'?  Who are you working for?  What's the place like ( I'm totally westernised, be honest, I won't take offence)?

"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Legacy Fan
  • ******
  • Posts: 36,042
  • .
  • Super Title: Southern Pansy
Re: The Microsoft Excel Help Thread
« Reply #82 on: November 6, 2014, 02:53:31 pm »
If you need to reuse the sheet at all, use a $A$1 reference for your boundary values between the categories. That then allows you to move the boundaries without changing every formula...

Offline kavah

  • the Blacksmith. Definitely NOT from Blackpool!
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 19,634
  • We all Live in a Red and White Kop
Re: The Microsoft Excel Help Thread
« Reply #83 on: November 6, 2014, 03:40:21 pm »
 ^ Thanks SP. One of my classmates mentioned that. But I'm very much excel 101. I'll have a go of that later. 10pm here. Time for a wee dram

Can't say I know either of them.

Are you there for 'work'?  Who are you working for?  What's the place like ( I'm totally westernised, be honest, I won't take offence)?



It's boss mate. I've never lived in Asia before. In fact I was in liverpool until I was near 40.
Saigon is a teeming metropolis with crazy mad-moped-traffic. The country is fascinating. They are Undergoing a technological and industrial revolution simultaneously while trying to embrace the free market system and negotiate free trade with ASEAN, the eu & the U.S.

The food is wonderful of course, it's a truly great cuisine, and the folks have a French or Italian like  reverence for eating and drinking, usually communally. I'm studying business with a class of mainly Vietnamese from all over the country and they are the nicest people. Hard working and friendly. Just a bit  naive about the outside world.
In short it's well worth the trip.the Language is fucking impossible though - I have had hundreds of hours and no one can understand a word I say - but I have got a very strong scouse accent :)

I'll send you a pm re. my circumstances, nothing mysterious like. Just not for the excel thread!

Offline Buck Pete

  • GV66 LJF for short. King Kong Balls. Bathes in peat. Partial to a walnut whip. Gets wet for 24/7 but disappointed Chopper. On the mortgage blacklist. Too tight to really be called a
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 30,081
Re: The Microsoft Excel Help Thread
« Reply #84 on: March 10, 2015, 12:32:10 pm »
Hi Guys

Any idea how i search for a specific key word within an excel spread sheet then place a specific value in the next cell of the row?

IE: search for "Liverpool" and then place a "5" in the cell next to Liverpool
or search for "Man Utd" and then place a "3" in the cell next to Man Utd
or search for "Everton" and then place a "0" in the cell next to Everton

You catch my drift? :)

There will be multiple occurrences of each word so I want to save some serious time

sure this is easy for someone who knows what they are doing.

cheers :wave

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Legacy Fan
  • ******
  • Posts: 36,042
  • .
  • Super Title: Southern Pansy
Re: The Microsoft Excel Help Thread
« Reply #85 on: March 10, 2015, 01:02:03 pm »
Hi Guys

Any idea how i search for a specific key word within an excel spread sheet then place a specific value in the next cell of the row?

IE: search for "Liverpool" and then place a "5" in the cell next to Liverpool
or search for "Man Utd" and then place a "3" in the cell next to Man Utd
or search for "Everton" and then place a "0" in the cell next to Everton

You catch my drift? :)

There will be multiple occurrences of each word so I want to save some serious time

sure this is easy for someone who knows what they are doing.

cheers :wave


Do you mean like this?

Where A1 is a cell reference being searched:
=IF(IFERROR(SEARCH("Liverpool", A1), 0), 5, 0)


Offline Titi Camara

  • Hey, wanna hear the new dubstep song I wrote? Wub, Wub, Wub! Wubba Lubba Dub Dub! I'm Pickle Rick with hirsute areolae!
  • RAWK Staff
  • Legacy Fan
  • ******
  • Posts: 19,211
  • Number 21 of the Crazy 88
Re: The Microsoft Excel Help Thread
« Reply #86 on: March 10, 2015, 01:08:58 pm »

Do you mean like this?

Where A1 is a cell reference being searched:
=IF(IFERROR(SEARCH("Liverpool", A1), 0), 5, 0)
That would only work if yiu know the specific range though wouldn't it?

To search a workbook you'd need to write a macro.

EDIT: if you know the range then this will do the job...

=IF(A1="Liverpool",5,IF(A1="Man Utd",3,IF(A1="Everton",0,"")))
« Last Edit: March 10, 2015, 01:11:45 pm by Titi Camara »

Offline Buck Pete

  • GV66 LJF for short. King Kong Balls. Bathes in peat. Partial to a walnut whip. Gets wet for 24/7 but disappointed Chopper. On the mortgage blacklist. Too tight to really be called a
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 30,081
Re: The Microsoft Excel Help Thread
« Reply #87 on: March 10, 2015, 01:40:56 pm »
Cheers Boys

That's put me in the right ballpark anyhow

:wave

Offline Red Raw

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 4,821
  • Klopptimistic
Re: The Microsoft Excel Help Thread
« Reply #88 on: March 10, 2015, 08:54:16 pm »
Hi Guys

Any idea how i search for a specific key word within an excel spread sheet then place a specific value in the next cell of the row?

IE: search for "Liverpool" and then place a "5" in the cell next to Liverpool
or search for "Man Utd" and then place a "3" in the cell next to Man Utd
or search for "Everton" and then place a "0" in the cell next to Everton

You catch my drift? :)

There will be multiple occurrences of each word so I want to save some serious time

sure this is easy for someone who knows what they are doing.

cheers :wave
You could also try VLOOKUP - you need a master table with all the values in and put the formula where you want the value to appear (see below).  It has the advantage that you can subsequently edit the master table and all the results will filter through into the worksheet(s).

Offline NANDO09

  • Main Stander
  • ***
  • Posts: 86
Re: The Microsoft Excel Help Thread
« Reply #89 on: April 1, 2015, 07:53:12 pm »
Hi,

I could do with some assistance with VBA. I have a range which i want to use as the base for cells below. For example i need range a1:d1 and J1:k1 to be replicated in the cells below. I want these to be populated via a loop for each row which has data in column H

Please see my code below:

Sub test()
Dim range1 As range
Set range1 = range("a1:d1,j1:k1")
range("h1").Select

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -7).Select
Cells.Value = range1
Loop
End Sub

The cell.value line is where I am struggling. Any assistance would be much appreciated



Offline smithy

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 2,011
Re: The Microsoft Excel Help Thread
« Reply #90 on: April 1, 2015, 08:30:50 pm »
try this...

Sub test()
    Dim range1 As Range
    Dim range2 As Range
    Dim count As Integer
    Set range1 = Range("a1:d1")
    Set range2 = Range("j1:k1")
    count = 2
   
    Do Until Range("H" & count).Value = ""
        Range("A" & count & ":d" & count).Value = range1.Cells.Value
        Range("j" & count & ":k" & count).Value = range2.Cells.Value
        count = count + 1
    Loop
End Sub


EDIT: Fixed for the 2nd range.
« Last Edit: April 1, 2015, 08:52:15 pm by smithy »

Offline NANDO09

  • Main Stander
  • ***
  • Posts: 86
Re: The Microsoft Excel Help Thread
« Reply #91 on: April 1, 2015, 11:37:17 pm »
try this...

Sub test()
    Dim range1 As Range
    Dim range2 As Range
    Dim count As Integer
    Set range1 = Range("a1:d1")
    Set range2 = Range("j1:k1")
    count = 2
   
    Do Until Range("H" & count).Value = ""
        Range("A" & count & ":d" & count).Value = range1.Cells.Value
        Range("j" & count & ":k" & count).Value = range2.Cells.Value
        count = count + 1
    Loop
End Sub


EDIT: Fixed for the 2nd range.

Thanks, that works a treat. Variables are an area which I struggle with. Just a quick question.

I've currently just completed my 10th month as an analyst and am gradually developing with VBA, my question is:

What would you say the best method for developing with VBA/ how would you suggest to develop with VBA?


Offline conman

  • Ohh aaaah just a little bit, Ooh aahh, a little bit more. Aerial stalker perv. Not cool enough to get the lolz.
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 27,498
    • Cocopoppyhead
Re: The Microsoft Excel Help Thread
« Reply #92 on: May 2, 2015, 01:01:14 pm »
help needed.

Is it possible to graph final positions of a race, so that 1st will be higher than 2nd, 3rd, etc.
The graphs by default put the larger number, higher up.

Offline Buck Pete

  • GV66 LJF for short. King Kong Balls. Bathes in peat. Partial to a walnut whip. Gets wet for 24/7 but disappointed Chopper. On the mortgage blacklist. Too tight to really be called a
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 30,081
Re: The Microsoft Excel Help Thread
« Reply #93 on: August 19, 2015, 11:20:31 am »
Guys - can someone please advise on this basic question.

I have opened a csv file in excel and the formatting is all over the place (see screenshot)

How go i get it formatted correctly please?


Offline Titi Camara

  • Hey, wanna hear the new dubstep song I wrote? Wub, Wub, Wub! Wubba Lubba Dub Dub! I'm Pickle Rick with hirsute areolae!
  • RAWK Staff
  • Legacy Fan
  • ******
  • Posts: 19,211
  • Number 21 of the Crazy 88
Re: The Microsoft Excel Help Thread
« Reply #94 on: August 19, 2015, 11:25:37 am »
Guys - can someone please advise on this basic question.

I have opened a csv file in excel and the formatting is all over the place (see screenshot)

How go i get it formatted correctly please?
Open a blank excel file.

Go to Data -> Get external Data -> From Text

Locate csv file you want to open....

make sure delimited is selected

then select the appropriate spacer (I think it's semi-colon, but can't quite tell due to picture quality)

hit finish

all done :wave

Offline Buck Pete

  • GV66 LJF for short. King Kong Balls. Bathes in peat. Partial to a walnut whip. Gets wet for 24/7 but disappointed Chopper. On the mortgage blacklist. Too tight to really be called a
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 30,081
Re: The Microsoft Excel Help Thread
« Reply #95 on: August 19, 2015, 11:31:35 am »
Titi - I love you

I hope you know that

(Yes it was a semi-colon)

Offline Titi Camara

  • Hey, wanna hear the new dubstep song I wrote? Wub, Wub, Wub! Wubba Lubba Dub Dub! I'm Pickle Rick with hirsute areolae!
  • RAWK Staff
  • Legacy Fan
  • ******
  • Posts: 19,211
  • Number 21 of the Crazy 88
Re: The Microsoft Excel Help Thread
« Reply #96 on: August 19, 2015, 11:32:34 am »
Titi - I love you

I hope you know that

(Yes it was a semi-colon)
Most work I've done all day that ;D ;D ;D :wave

Offline tubby

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 25,082
  • Destroyed Cowboy
Re: The Microsoft Excel Help Thread
« Reply #97 on: January 14, 2016, 04:06:45 pm »
I'm sure there's an easier way of doing this (maybe a vlookup) but I can't get my brain working today and am having trouble with a formula.

Currently is: =IF(F4="JNR Novel", (I4/2400)*Rates!$E$18, 0)

What I want is another IF statement that references C4 and if it finds 'EN-UK' in there, then the value returned is also 0, regardless of what F4 equals.

EDIT:  Nevermind, sussed it.
« Last Edit: January 14, 2016, 04:14:54 pm by tubby »
Sit down, shock is better taken with bent knees.

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: The Microsoft Excel Help Thread
« Reply #98 on: May 8, 2016, 03:00:13 pm »
yesterday, I was using Excel office starter 2010. Today when I try to open it, it's saying Click-2-run configuration failure.

Treid doing the repair thing on the control panel and get the same message.

When I try to open the spreadsheet I was using yesterday it brings me to the page to buy the full Office program, which I don't want to do as I don't need it that much and rarely will use it bar for this simple thing that I'm doing now.

Any ideas how to fix this?
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline Party Phil

  • Boring Cunt that flies Air Bizarre
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 12,552
  • Big in Japan
Re: The Microsoft Excel Help Thread
« Reply #99 on: May 9, 2016, 06:32:10 am »
Download Open Office
If you're lying, I'll chop your head off.

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: The Microsoft Excel Help Thread
« Reply #100 on: May 9, 2016, 09:10:14 am »
Is that free?
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: The Microsoft Excel Help Thread
« Reply #102 on: July 29, 2016, 10:50:14 pm »
I need help. I'm looking to create a scoresheet/league table on Open Office. However, instead of getting 3 points for a win, I want to make it total score +3 points for a win, or total score +1 point for a draw

EG

I win a tie 18-11. I want to be able to enter the score as 18-11, and the table to score me 21 points and the loser 11 points.
I draw 15-15, I want the table to award each player 16 points.

Anyone know how to do this?
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Legacy Fan
  • ******
  • Posts: 36,042
  • .
  • Super Title: Southern Pansy
Re: The Microsoft Excel Help Thread
« Reply #103 on: July 29, 2016, 11:05:11 pm »
Score 1 in column A
Score 2 in column B

=IF(A2>B2;3;IF(A2=B2;1;0))+A2
=IF(B2>A2;3;IF(A2=B2;1;0))+B2

First line is Player 1 points, second is Player 2 points - clearly for row 2.

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: The Microsoft Excel Help Thread
« Reply #104 on: July 30, 2016, 08:29:47 pm »
Cheers SP.

Now can you tell me how to make a fixture list  ;D
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: The Microsoft Excel Help Thread
« Reply #105 on: August 13, 2016, 09:26:04 pm »
OK geniuses, I need help again

https://docs.google.com/spreadsheets/d/1sCGsCZsqpjaxnlmMcN42KQbm1mKwEbhS8C6Aj4Sy6bI/edit#gid=1638831760

I've created the above. The column marked 'Pts' will determine league positioning. How do I change this so that it's the column marked 'Total' that determines league places.
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline terrible suits

  • Formerly: terrisus
  • RAWK Supporter
  • Kopite
  • ******
  • Posts: 989
  • Justice for the 96 - You'll Never Walk Alone
    • terrisus.com
Re: The Microsoft Excel Help Thread
« Reply #106 on: August 13, 2016, 09:34:38 pm »
OK geniuses, I need help again

https://docs.google.com/spreadsheets/d/1sCGsCZsqpjaxnlmMcN42KQbm1mKwEbhS8C6Aj4Sy6bI/edit#gid=1638831760

I've created the above. The column marked 'Pts' will determine league positioning. How do I change this so that it's the column marked 'Total' that determines league places.

Select the upper-left cell (the first '1' underneath 'R'), hold Shift, select the lower-right cell (the last '0'), let go of Shift, Right-click on the selection, choose "Sort range," in the box next to "Sort by" where '1' is currently selected, click it and choose the last '0' (the one after "column M"). Make sure A->Z is still selected, and hit "Sort"
« Last Edit: August 13, 2016, 09:36:20 pm by terrible suits »

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: The Microsoft Excel Help Thread
« Reply #107 on: August 13, 2016, 10:39:18 pm »
Select the upper-left cell (the first '1' underneath 'R'), hold Shift, select the lower-right cell (the last '0'), let go of Shift, Right-click on the selection, choose "Sort range," in the box next to "Sort by" where '1' is currently selected, click it and choose the last '0' (the one after "column M"). Make sure A->Z is still selected, and hit "Sort"

Cheers, but do you know how I set that to a default rule, as it keeps reverting back to the previous way when I go off that tab
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline terrible suits

  • Formerly: terrisus
  • RAWK Supporter
  • Kopite
  • ******
  • Posts: 989
  • Justice for the 96 - You'll Never Walk Alone
    • terrisus.com
Re: The Microsoft Excel Help Thread
« Reply #108 on: August 13, 2016, 11:10:53 pm »
Cheers, but do you know how I set that to a default rule, as it keeps reverting back to the previous way when I go off that tab

Not sure honestly, not as familiar with Google's office suite. In Excel it'll just stay that way.

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: The Microsoft Excel Help Thread
« Reply #109 on: August 13, 2016, 11:22:50 pm »
Not sure honestly, not as familiar with Google's office suite. In Excel it'll just stay that way.

Ta mate, I'll plough on anyway until I figure it out. Small steps at this excel shit. It's a fucking melt  ;D
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Online PaulF

  • https://www.justgiving.com/fundraising/paulfelce
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 21,575
  • Nothing feels as good as fat tastes.
Re: The Microsoft Excel Help Thread
« Reply #110 on: August 19, 2016, 03:05:35 pm »
Ta mate, I'll plough on anyway until I figure it out. Small steps at this excel shit. It's a fucking melt  ;D
You could make a copy of the document and share it so we can have a play.
Google Docs is a different beast admittedly...
"All the lads have been talking about is walking out in front of the Kop, with 40,000 singing 'You'll Never Walk Alone'," Collins told BBC Radio Solent. "All the money in the world couldn't buy that feeling," he added.

Offline Craig S

  • KOP CONDUCTOR
  • Legacy Fan
  • ******
  • Posts: 4,979
Re: The Microsoft Excel Help Thread
« Reply #111 on: August 19, 2016, 04:15:30 pm »
You will need to do it in the calcs sheet (which is hidden).
There will be a rank for each row in that sheet. And then something like a lookup function that will put them in order.
You need to change the rank cells to look at the "total" column

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: The Microsoft Excel Help Thread
« Reply #112 on: August 22, 2016, 12:55:58 am »
Here's a new one

https://docs.google.com/spreadsheets/d/1hrEFHHVq0cWBfubZjt9PQPwrr2cW4BektW2-Q1joueE/edit#gid=1638831760

I want the column 'F' on the table tab, to automatically update with the total scores I enter on the scores tab.

So next week, if I enter a 10 in cell F4 on the scores tab, I want Sarges F column to auto change to 33.

I had a look at the calcs sheet, and it means nothing to me  ;D If someone knows what they're doing, PM me and I'll open it up for you to fuck about with  ;D
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Online Crosby Nick

  • He was super funny. Used to do these super hilarious puns
  • RAWK Scribe
  • Legacy Fan
  • ******
  • Posts: 111,066
  • Poultry in Motion
Re: The Microsoft Excel Help Thread
« Reply #113 on: August 22, 2016, 01:02:33 am »
Here's a new one

https://docs.google.com/spreadsheets/d/1hrEFHHVq0cWBfubZjt9PQPwrr2cW4BektW2-Q1joueE/edit#gid=1638831760

I want the column 'F' on the table tab, to automatically update with the total scores I enter on the scores tab.

So next week, if I enter a 10 in cell F4 on the scores tab, I want Sarges F column to auto change to 33.

I had a look at the calcs sheet, and it means nothing to me  ;D If someone knows what they're doing, PM me and I'll open it up for you to fuck about with  ;D

Hard to explain on my phone but you just need to make sure the range in the F column includes all the cells for the season, not just what you have there ar the moment. Normally if you hover the mouse over the bottom right of the last cell you have showing in the range you can drag it manually to include more cells if you wish.

Apologies if that reads as gibberish!  Can do it on a laptop in the morning if someone hasn't sorted it by then!

Offline Barneylfc∗

  • Cross-dressing man-bag wielding golfer. Wannabe Mod. Coprophiliac. Would like to buy an airline seat if he could. Known 'grass'. Wants to go home to He-Man
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 59,484
Re: The Microsoft Excel Help Thread
« Reply #114 on: August 22, 2016, 01:37:43 am »
A shit load of trial and error and I've got it sorted  :champ
Craig Burnley V West Ham - WEST HAM WIN - INCORRECT

Offline GinKop

  • He's going to run to you (ooh-oooh). Knows a techtard when he sees one
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 13,763
  • FORZA MILANO
Re: The Microsoft Excel Help Thread
« Reply #115 on: August 28, 2016, 09:15:27 pm »
Evening all, got an Excel conundrum. I do not know how to do anything advanced on Excel so struggling with this.

I have a large monetary figure (to the pence) which needs to randomly divided across days and months of a calander year. My first thought was to try Excel but some googling of the problem has not given me the answer.

Is anyone able to provide an idea on how I could do this?
JFT97

Offline SP

  • Thor ain't got shit on this dude! Alpheus. SPoogle. The Equusfluminis Of RAWK. Straight in at the deep end with a tube of Vagisil. Needs to get a half-life. Needs a damned good de-frag.
  • RAWK Staff.
  • Legacy Fan
  • ******
  • Posts: 36,042
  • .
  • Super Title: Southern Pansy
Re: The Microsoft Excel Help Thread
« Reply #116 on: August 28, 2016, 09:40:02 pm »
Evening all, got an Excel conundrum. I do not know how to do anything advanced on Excel so struggling with this.

I have a large monetary figure (to the pence) which needs to randomly divided across days and months of a calander year. My first thought was to try Excel but some googling of the problem has not given me the answer.

Is anyone able to provide an idea on how I could do this?



Put money total in A1.

Put day labels in row 1.

In row 2 for column B onwards us the formula =Rand()
A2 has formula. =sum(b2:ee2)

In b3 =b2*$a$1/$a$2

Drag b3 across horizontally.

You may need to adjust the last day to take into account the rounding errors from the calculation

Offline GinKop

  • He's going to run to you (ooh-oooh). Knows a techtard when he sees one
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 13,763
  • FORZA MILANO
Re: The Microsoft Excel Help Thread
« Reply #117 on: August 28, 2016, 09:48:11 pm »


Put money total in A1.

Put day labels in row 1.

In row 2 for column B onwards us the formula =Rand()
A2 has formula. =sum(b2:ee2)

In b3 =b2*$a$1/$a$2

Drag b3 across horizontally.

You may need to adjust the last day to take into account the rounding errors from the calculation

Thank you so much for helping.

I am getting 0.00 when I drag across from b3, does that mean I have input the formula incorrectly?
JFT97

Offline Thush

  • Spawwow, Tit. Anal Chat is "Equidistant between chit-chat and analysis"
  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 3,072
  • It's pronounced "Toosh"
Re: The Microsoft Excel Help Thread
« Reply #118 on: August 28, 2016, 10:53:15 pm »
SP's method does work (just tried it). I did this:

1. In A1, put your money value
2. In B1, put "01-Jan" and drag along the row until you get to "31-Dec" (should be cell NC1)
3. In A2, put "=SUM(B2:NC2)"
4. In B2, put "=RAND()" and drag along the row until you get to 31-Dec (should be cell NC2)
5. You should see the total in cell A2 change (this is fine)
6. In B3, put "=B2*$A$1/$A$2" and drag along the row until you get to 31-Dec (should be cell NC3) -> these are your money values per day
7. You can double check this by summing B3:NC3

Offline Red Raw

  • RAWK Supporter
  • Legacy Fan
  • ******
  • Posts: 4,821
  • Klopptimistic
Re: The Microsoft Excel Help Thread
« Reply #119 on: August 29, 2016, 01:31:36 pm »
A couple of things you should possibly be aware of using Excel's RAND() function:
  • In earlier versions of Excel (pre-2010) the numbers aren't properly random
  • Excel will recalculate every cell with RAND() in it whenever you change any other cell

If either of these are of any concern you can:
  • Use an online generator e.g. https://www.random.org/ this will generate a custom range of sequences of integers or decimals that are truely random (based on atmospheric noise)
  • Use the above to get your random number, or copy and paste the Excel generated cells as 'values' rather than functions (which tends to be the default)