|
Write a macro to generate a table of your random values.
|
#
?
Sep 28, 2020 14:40
|
|
|
|
| # ? May 11, 2024 23:56 |
|
|
disaster pastor posted:I'm currently using a very basic formula for weighted averages: (B2*0.1)+(C2*0.1)+(D2*0.1)+(E2*0.2)+(F2*0.25)+(J2*0.25) The quick and dirty method is to generate each random number individually then scale them all together at the end to sum to one. E.g. You generate a "first set" of random numbers based on your chosen method. [0.08, 0.1, 0.05, 0.2, 0.25, 0.3] Which sums to 0.98. Divide each element by 0.98 to generate a second set of random numbers: [0.816, 0.102 ...] Then you ignore the first set of random numbers (which doesn't sum to 1), and use the second set (which does to 1) as your weights. The way to implement it in excel is to use a bunch of columns off to the right for each row, or use a macro. I couldn't gather from your post whether each weight is uniform from 0-100% or if it is constrained in some way (e.g. weight on B is expected to be normal with mean 0.1 and stdev 0.02) but this method works under any approach to generating the random weights. Note that this is not mathematically rigorous and probably would not fly in an academic setting - it takes whatever probability distributions you start out with and "tweaks" them in order to fit into the constraints.
|
|
#
?
Sep 28, 2020 14:56
|
|
|
Thank you both! Unfortunately, I'm not very good at macros, but esquilax's method got me really close to what I was looking for.esquilax posted:I couldn't gather from your post whether each weight is uniform from 0-100% or if it is constrained in some way (e.g. weight on B is expected to be normal with mean 0.1 and stdev 0.02) but this method works under any approach to generating the random weights. This is a new project, so the original weights were based on "what makes this formula come close to actual results in the data I already have?" Now I have them as RANDBETWEEN(X,Y)/10000, where X and Y are eyeballed based on the original weight's value (and divided by 10000 to get them between 0 and 1). esquilax posted:Note that this is not mathematically rigorous and probably would not fly in an academic setting - it takes whatever probability distributions you start out with and "tweaks" them in order to fit into the constraints. Good thing I'm in a corporate setting instead of an academic setting. 
|
|
#
?
Sep 28, 2020 17:12
|
|
|
In that case, one caveat from my method is that the tweaks it does to probability distributions might generate weights outside of your range. E.g. if your randbetween() is designed to constrain the B weight randomly between 0.05 and 0.15, it might end up using something like 0.04 or 0.18 (which would be outside of the 0.05 to 0.15 range). It sounds like that's ok based on what you've said.
|
|
#
?
Sep 28, 2020 17:42
|
|
|
esquilax posted:In that case, one caveat from my method is that the tweaks it does to probability distributions might generate weights outside of your range. E.g. if your randbetween() is designed to constrain the B weight randomly between 0.05 and 0.15, it might end up using something like 0.04 or 0.18 (which would be outside of the 0.05 to 0.15 range). It sounds like that's ok based on what you've said. Yup, that's totally fine, but thank you for pointing it out!
|
|
#
?
Sep 28, 2020 18:17
|
|
|
I'm doing the stats for my dart league this year. I have it all done nice and tidy, with a sheet for each player and the main sheet will reference each player's sheet to show their average. I also have columns for certain scores (tons, 180s for example if you are familiar with darts). These columns are whats throwing me for a loop. What kind of formula am I looking for to achieve this? Say for example, the tons cell in a players row on the main page will reference their entire sheet and return the number of values greater than 100.
codo27 fucked around with this message at 16:14 on Oct 3, 2020 |
|
#
?
Oct 3, 2020 16:11
|
|
|
codo27 posted:I'm doing the stats for my dart league this year. I have it all done nice and tidy, with a sheet for each player and the main sheet will reference each player's sheet to show their average. I also have columns for certain scores (tons, 180s for example if you are familiar with darts). These columns are whats throwing me for a loop. What kind of formula am I looking for to achieve this? Say for example, the tons cell in a players row on the main page will reference their entire sheet and return the number of values greater than 100. You want something like =COUNTIFS(A:A, ">=100") -- that will give you how many cells in column A are greater or equal to 100.
|
|
#
?
Oct 3, 2020 23:15
|
|
|
What do you mean Excel doesn't have unlimited rows? https://arstechnica.com/tech-policy/2020/10/excel-glitch-may-have-caused-uk-to-underreport-covid-19-cases-by-15841/ Possibly 16k UK covid cases went untracked due to exceeding spreadsheet row limits. Hughmoris fucked around with this message at 20:00 on Oct 5, 2020 |
|
#
?
Oct 5, 2020 19:52
|
|
|
Whybird posted:You want something like =COUNTIFS(A:A, ">=100") -- that will give you how many cells in column A are greater or equal to 100. This worked though, logically, I ended up having to make it >99 to include the even 100 values as well.
|
|
#
?
Oct 5, 2020 21:54
|
|
|
Sorry for the double post, but to get your attention. With my darts scores again, I have been asked to add another column, I'd like to add two actually. To recap, I have a main page with every player listed and their average, tons, etc. I want to add a column that will show their most recent weekly average. Note that there are no dates in here, it just says Week 1/2/3 etc in the first column. In each players sheet, each week is a row with their total average for the week in a column at the end. So I'd like a formula, if possible, that will reference this column and each week when I enter new values, it will show that average on the main sheet, rather than having to copy that value and paste it in each time I do it, for each player. Doubly useful when a player misses a week I'd also like one that shows the highest average out of all the weeks for each player. I thought I had this using =MAX along with IFERRORS arguments as this averages column in the player's sheet is completely filled out, its just for all the unplayed weeks it shows #DIV/0!. It worked for the first couple of players but then threw up ##### for the ones after even though I double checked and its exactly the same, with just the value of the player's sheet title changing. It was something like this =MAX(IFERROR(Codo!BN2:BN36, "")) Greatly appreciate the help
|
|
#
?
Oct 28, 2020 23:39
|
|
|
I'm not sure exactly where you're calculating these but if you're calculating the average for a given week in the player's sheet then pulling that into the main summary you can use the iferror with the average so the error never shows. So something like =IFERROR(AVERAGE(<range of values for the week>),""). Then if you create a total average on the main summary, Excel will ignore the blanks. This will also lend itself to finding the highest average because then you can use large and reference your range of weekly averages, something like =LARGE(<range of averages>,1). The second parameter tells it to return the largest value in the range if = to 1, second largest if = to 2 and so forth.
|
|
#
?
Oct 31, 2020 16:06
|
|
|
I'm losing my mind with dates. I have a column displaying dates in mm/dd/yyyy. I am trying to split them into three columns, using the slash as delimiter. No matter what I try, the result is three columns that display: code:What the hell am I doing wrong? I have the date column formatted as date, along with the columns next to it. When I do text to columns, I am specifying each column during that operation to be formatted date. Nothing seems to work. I have tried moving the date column to another column in the spreadsheet, I have tried bringing the column into Notepad++ to strip any formatting and pasting it back in.
|
|
#
?
Nov 3, 2020 15:04
|
|
|
Dates in Excel are calculated as time since X date so what I think is happening is you're successfully splitting the date into "1", "3", and "2020" but it's being read as 1, 3, and 2,020 days since New Year's 1900. Try instead creating a date column and then use day(), month(), and year() functions to extract the numbers you need. Good luck!
|
|
#
?
Nov 3, 2020 17:54
|
|
|
me your dad posted:I'm losing my mind with dates. I have a column displaying dates in mm/dd/yyyy. Excel views the values 1, 3, and 2020 as those dates. You can use the day(), month() and year() functions as previously suggested, or just change the formatting on those columns. To do this, you can use the Home->Number dropdown menu to on those three columns change the format from "Date" to either "Number" or "General". The shortcut for this is Ctrl+Shift+~
|
|
#
?
Nov 3, 2020 18:02
|
|
|
Thank you both - I changed the format of the date to general, and it changed it to the numerical string for the date. Then on the adjacent cells I used the Month() Day() and Year() functions to retrieve them. I didn't know about using those.
|
|
#
?
Nov 3, 2020 20:30
|
|
|
I need to count the number of distinct values in a table based on a condition. I found various formula examples online, but they hard code the row range in the formula. What I would like to do is create a template instead where the raw data is copy/pasted into a sheet and another sheet does the formula calculation, so I can't hard code the row range because it will vary. Here's an example formula (from: https://www.get-digital-help.com/count-unique-distinct-values-based-on-a-condition/). They used this array formula: code:code:Edit: This is on Office 2016, so I can't use some of the new fancy formulas in O365 that I also found. Secx fucked around with this message at 18:02 on Nov 9, 2020 |
|
#
?
Nov 9, 2020 17:52
|
|
|
Secx posted:Is there another syntax I can use to avoid hard coding the rows? My columns will always be the same when I paste my data. It's the number of rows that I can't control. code:code:
|
|
#
?
Nov 10, 2020 05:53
|
|
|
Is it possible to filter an inlist based on the value provided from another inlist? I have a set of data akin to this: code:
|
|
#
?
Nov 21, 2020 09:15
|
|
|
Dependent dropdowns? https://exceljet.net/dependent-dropdown-lists
|
|
#
?
Nov 21, 2020 20:49
|
|
|
Wandering Orange posted:Dependent dropdowns? https://exceljet.net/dependent-dropdown-lists Thanks! That's definitely the avenue that I was looking for, though it looks like - if I want a dynamic number of rows and more than two dependent drop-downs I may need to look into VBA. Time I dug my teeth into that anyway
|
|
#
?
Nov 23, 2020 03:35
|
|
|
Hey y'all I've got some hosed up data I'm working with and I fear if I keep at this on my own much longer the repeated stress of cranial impact is going to eventually shatter my desk to pieces. I (currently) quote co-delivery service contracts for a living. This involves me going into our vendor's website and putting together a quote using their pricing tool. Once I've got the vendor quote in-hand, I then have to take the guts of that quote and put it into a quote template that my company uses, and then present that to the customer. I can get the vendor quote as an xml file (some kind of weird-rear end xml file that needs some sort of remote auth key for me to even try and do anything in python with it, so that's out), as a live html page, a csv file, or an xlsx document. Since it's all just tables anyway, I usually pull down either the HTML version or the XLSX version. Easy-fuckin-peasy, copy paste from one workbook to the other, right? loving wrong. Look at this poo poo. Look at this absolute horseshit we get from our vendor:  20 columns of data, spread out across sixty-loving-six columns - with most of them loving hidden. What's in them, you ask? Absofuckinglutely nothing. They're blank. Not a loving thing in them, Just resized down to 0.0001 pixels wide, and merged and centered across the range.  Of course, when I try and paste this data into a clean sheet, it's an abhorrent mess of empty cells that don't even line up properly (see row 1 as compared to the remaining rows) :  I can, fairly efficiently, put in the manual work to unfuck it - punch row 1 over into column b, multi-select empty rows and columns, remove, blah blah blah. This is making me insane. It's a bug-gently caress-nutty time sink and it drags our entire team down. I don't want to do it manually anymore. So far: I've tried macros, which have helped to automate some of the process - the number of blank columns is always consistent so those are easy to account for in a macro. Unfortunately, the variable number of line items (and tables full of line items - there can be multiple sites in a quote, each with their own table of line items) make removing the rows an imprecise science at best. I've tried a little bit of python but I'm still very new to it, I thought about using a for loop but the inconsistent number of blank rows/columns that need to be removed is a bit beyond my level of understanding right now. Ultimately, I want the most simple, plain table of data you could ask for.  What do you think would be the best way to approach something like this, save finding whomever designed the format for these quotes and holding their head underwater until that one last bubble "bloop"s its way to the surface?
|
|
#
?
Dec 16, 2020 18:38
|
|
|
Have you tried anything with Power Query yet?
|
|
#
?
Dec 16, 2020 19:34
|
|
|
I get to deal with this sort of stuff for work every now and then and congrats - that’s some of the worst data I’ve seen. My first step would be to try all the export file types and see if any of them are a bit cleaner once pulled into Excel, but I’m kind of doubtful with what you have here. Next step would be open the file, run the below to remove line breaks, carriage returns and then see how you’re looking, I think this might fix some of the main issues. code:If you still need to do more work, the best general advise I can offer: *Save your code as you work in Personal Macro Workbook so it’s available whenever you need it without finding the file you saved it in. *Record a macro of you cleaning it up, then you can convert this code to repeatable actions. Like if you always delete columns B, C, E. You can have this in your cleanup macro.
|
|
#
?
Dec 16, 2020 20:05
|
|
|
Harminoff posted:Have you tried anything with Power Query yet? I adore Power Query. I use it when I don't feel like spinning up wamp and doing things with SQL - though I'll admit the way the raw data comes out had me thinking that Power Query would just...poo poo itself trying to do anything with it. I may give it a go DRINK ME posted:I get to deal with this sort of stuff for work every now and then and congrats - that’s some of the worst data I’ve seen. This is all MCode, yeah? Or is this VBA? I've really only used Power Query to emulate SQL (that's my real fuckin' jam) for purposes of like, customer inventories and the like so I'm not familiar really with the code - of course with data this lovely, I don't even want to begin trying to load it into a db until it makes something resembling sense. I do have a personal macro that removes the columns I don't need (I really am only concerned with 4 of them - Service Description, Service Code, Svc Quantity, and Total List Price) as well as the empties so, I have the start of something. I actually brought the CSV version of the export over to the python thread a while back and I got essentially the same reaction to that: 100%  so, yeah. Fun. It looks a bit like this:code:
|
|
#
?
Dec 16, 2020 20:37
|
|
|
VBA sorry. Wasn’t even thinking of power query, just hoping removing all those like breaks maybe get your data on the same lines to start with
|
|
#
?
Dec 16, 2020 21:43
|
|
|
Fun! I've never done VBA before. I was thinking of grabbing a Udemy class on it, I think I will next time they have a sale. You talked me into it.
|
|
#
?
Dec 16, 2020 21:47
|
|
|
do you have a rough example file? if i had to deal with that using only excel first thought would be to make a parser sheet pointing at the downloaded file as a named range then try match(1,index(column_name)) for each uniquely named column. i gather the data is structurally under the column but messes up when you're copy/pasting. if you threw together a naive parser for the first 100 rows it'd give you a more set table to copy out of and filter out the empty rows running off to vba when you could abuse formulas seems rather common, what is their csv format fuckup that excel is having issues with it anyway?
|
|
#
?
Dec 16, 2020 21:56
|
|
|
Wiggly Wayne DDS posted:do you have a rough example file? if i had to deal with that using only excel first thought would be to make a parser sheet pointing at the downloaded file as a named range then try match(1,index(column_name)) for each uniquely named column. i gather the data is structurally under the column but messes up when you're copy/pasting. if you threw together a naive parser for the first 100 rows it'd give you a more set table to copy out of and filter out the empty rows I definitely wanted to over-engineer a solution. If you open the csv in Excel like the vendor intends, it actually displays almost exactly the way I wanted to Rube Goldberg it to.  Ain't that something. I was dead set on using it like this:  Still probably going to take a VBA course, another handy skill to have I'm sure Lib and let die fucked around with this message at 22:51 on Dec 16, 2020 |
|
#
?
Dec 16, 2020 22:10
|
|
|
Glad you got there the easy way mate. I’m very guilty of using VBA when formulas will do the same job but I do temper that compulsion when I’m doing work others have to use.
|
|
#
?
Dec 17, 2020 01:23
|
|
|
I have a table called Table1. =Table1[#Headers] returns Question Topic Max Mark Student1 Student2 Student3 How do I get it just to return from Student1 (index 4) to the end? We can assume that it will always start at index 4. In Python it'd be some form of my_list[3:] I'm trying to create a dropdown box using data validation and the options being the student names.
|
|
#
?
Dec 19, 2020 15:08
|
|
|
Does something like: =OFFSET(Table1[#Headers],0,3,1,COLUMNS(Table1[#Headers])-3) do what you need? I think you'll need to use a named range to use it in a dropdown though.
|
|
#
?
Dec 19, 2020 19:57
|
|
|
Ninja.Bob posted:Does something like: Thank you! That worked perfectly. Put that in M2 and then used Data Validation -> List -> =M2# edit - Follow up question... Is it possible to use formulas inside the structured references? Cell D4 contains the dropdown, so for example 'Bob'. On selecting in that dropdown, I'd like it to show =Table1[Bob] Using =INDIRECT("D4") would be my normal go to, but that doesn't seem to work inside the [] of a structured reference. edit 2 - Solved it. =OFFSET(Table1[#Data], 0, MATCH($D$4, Table1[#Headers], 0) -1, ROWS(Table1)-4, 1) Sad Panda fucked around with this message at 21:51 on Dec 19, 2020 |
|
#
?
Dec 19, 2020 20:19
|
|
|
Just changed companies after 6 years (one cpg for another if it matters), so all sorts of new files and systems to dig into. One in particular, a daily updated sales file, has one data tab (pulled in from bex analyzer) that feeds about a dozen other tabs and pivot tables. This company likes using slicers, so most of the data viewing tabs connect multiple slicers (regions and time periods) to multiple pivot tables (dollars and units). So when updating the data each day, it's a chore to unlink the slicers, change data source on the pivots, then re-link the slicers. So I'm looking for some faster ways to do this. I know VBA is one option and that's fine if it needs to get done that way (I know these keyboard shortcuts by heart now), but I'm wondering if there's an easier way to update the pivots without having to unlink/relink the slicers? Could I use a named range to define the raw sales data and just update that once? I'm just not that familiar with how slicers interact. Any help would be appreciated, thanks!!
|
|
#
?
Dec 29, 2020 16:12
|
|
|
That sounds a bit strange. If it’s just the source data that’s changing / being refreshed, can’t you just refresh your pivots, which will also refresh the slicers and you’re done? I feel like there’s a detail missing in your description?
|
|
#
?
Dec 29, 2020 23:27
|
|
|
Is there a way to get Excel to stop doing..."intelligent" things like trying to determine the Specific example: I'm pasting 4 values from a webpage into an excel sheet. Product name, version, quantity, and a 4th field that's useless that I end up deleting but have to copy over initially so the formatting stays right. The version field can contain a 7, an 8, or an "8/7" because the license works with both versions of the software. Excel thinks I'm trying to paste in 7-Aug. Ok, so format cells, general, no, that doesn't work, format cells, text, and...Excel converts it to a loving unicode date so I've got to go back in and manually enter "8/7" Yes I can find/replace after the fact, but, I don't want to. E: specifically a "now and for ever" solution, not something I have to pre-configure every time I make a new work book Lib and let die fucked around with this message at 20:30 on Dec 30, 2020 |
|
#
?
Dec 30, 2020 20:26
|
|
|
I think you’re stuck with having to fix that one. From memory there’s nothing in the options to sort it and I think the only way to deal with it is what you’re doing. Depending on the pain level you could probably code something up - If you are using the same workbook + worksheet you could have it fire with the On Change event and it would automatically correct it. Otherwise maybe stick it in your Personal Macro Workbook and run it yourself. Neither is a great solution but unfortunately Excel is just too smart for us users sometimes.
|
|
#
?
Dec 30, 2020 21:38
|
|
|
If you pre-format the column as a text column and paste as values/match destination formatting, does that prevent it from happening? My question: So I've got this one file called history.xlsm that contains sheets for different employees with the history of their raises, etc, and it's got a table called summaryTable that uses this bit of code (the AllSheets one, not the plugin whatever) to basically create a table of contents of every sheet in the workbook and also whatever everyone's current rate of pay is. I also have another file called whatever.xlsm that uses a table called wageInput of everyone's current rate of pay to calculate a whole bunch of other poo poo in other sheets. Right now the fastest way to make sure whatever.xlsm is up to date is to open up history.xlsm, copy the contents of summaryTable, and then paste those contents into wageInput. But I'm LAZY and I just wanna push a BUTTON that's shaped like a DUCK for some reason. In general, I know how to open another workbook, copy some data from it, and then close it, but I am running into problems first of all with the AllSheets named function I'm using, which sometimes borks and pulls info from whatever.xlsm instead of history.xlsm (bad), and second of all with copy-pasting specifically from summaryTable into wageInput as named ranges.
|
|
#
?
Dec 30, 2020 23:19
|
|
|
Should be able to import with power query and than refresh that to get updated data. No need to copy/paste
|
|
#
?
Dec 30, 2020 23:30
|
|
|
Lib and let die posted:Is there a way to get Excel to stop doing..."intelligent" things like trying to determine the A bunch of genes were renamed earlier this year because Excel kept reading them as dates. It was agreed to be easier to change a what a whole branch of science referred to things as than get Excel to stop doing that.
|
|
#
?
Dec 31, 2020 02:14
|
|
|
|
| # ? May 11, 2024 23:56 |
|
|
Big Bad Beetleborg posted:A bunch of genes were renamed earlier this year because Excel kept reading them as dates. It was agreed to be easier to change a what a whole branch of science referred to things as than get Excel to stop doing that. Well it's nice to know this isn't a problem relegated to morons like me. Really highlights the extremes of the love/hate relationship I have with Excel.
|
|
#
?
Dec 31, 2020 02:21
|
|