|
RICHUNCLEPENNYBAGS posted:You can use the ROUND() function to say how many decimal places you want to go to. ShimaTetsuo posted:Just put the rate formula in the cell ((F8-E8)/E8) or whatever), then set the number format to "Custom", and put in something like "+0.00%;-0.00%". But in either vase, I've hit another bump. I'm trying to set up the spreadsheet so the text turns red when the formula returns a -ve value, and green when it returns a positive value. But it's only showing green regardless of whether it's positive or negative: I think the conditional formatting isn't working because Excel doesn't see it negative or positive. It sees it as a digit, and since we're concatenating a + or - symbol, it can't tell the difference. Any suggested fixes for this?
|
# ? Jul 14, 2013 23:54 |
|
|
# ? May 13, 2024 10:20 |
|
I don't have a copy of 2003 to test on right now, but it definitely works in 2007. Your conditional formatting doesn't work for the same reason I advised against the "concatenate" method: your result is not a number, it is a string, and it will not respond properly to checking whether it is positive. What exactly does it return if you put in, say, 0.123456 in a cell, and set its custom format to: +0.00%[Green];-0.00%[Red]
|
# ? Jul 15, 2013 01:57 |
|
Nvm, wrote my own VBA code. Ragingsheep fucked around with this message at 03:41 on Jul 15, 2013 |
# ? Jul 15, 2013 02:05 |
|
ShimaTetsuo posted:What exactly does it return if you put in, say, 0.123456 in a cell, and set its custom format to:
|
# ? Jul 15, 2013 05:15 |
|
How easy would it be to take a user-defined function that accepts two values and modify it to work with arrays? I found a great function that compares the similarity of two strings and gives it a score, but I need to compare one string and a list of strings and return the best score. Unfortunately, shift+ctrl+enter didn't work, so I must have to alter the code.
|
# ? Jul 19, 2013 02:11 |
|
If I'm not misunderstanding you, then unless you have a shitton of rows it would be faster to just get it working with one row and auto-fill than rewrite the function to get it working with array arguments.
|
# ? Jul 20, 2013 22:50 |
|
He wants the function to return the best score in the list, not the list of scores. Don't rewrite the function, just write another function that takes in one string and one array of strings, loops through the array, calling your original function on each element and keeping a running maximum.
|
# ? Jul 20, 2013 23:06 |
|
Or =MAX() ? You'd have to use cells to store values but it would be one less UDF.
|
# ? Jul 20, 2013 23:45 |
|
Yeah, I basically wanted to take one list, apply two checks for each item on another list. The "similarity" check and a native Excel function that I normally use with ctrl+shift+enter. I ended up combining both into a new custom function.
|
# ? Jul 21, 2013 02:23 |
|
I have the first two columns and want to generate the second two with formulae. ParentID: All attachments have the word attachment in their filename, so I managed to luck into something using COUNTIF to return the value of B2 when A3 contains that string. I've been trying for a while, but can't seem to adequately nest statements to make it check if the field above has a value. Because everything is sorted alphabetically, if the cell above is not equal to zero and the filename contains attach, the cell should be the same as the one above. Current behaviour is to copy the ID of the previous row regardless of whether that is also an attachment or not. AttachID: If a document is referred to in the ParentID field of a different row, I want to list all of those ID's in the attachID field separated with a semicolon or a semicolon and a space. This one I haven't looked at too hard, because it's meaningless without an accurate parentID column. While I'm at it, is there a reference book or anything that someone can point me at for teaching myself this?
|
# ? Jul 21, 2013 04:06 |
|
I know this probably isn't helpful, but with IDs and such as you have them, do you think maybe you really want to use a database for this?
|
# ? Jul 21, 2013 11:42 |
|
It looks like you're trying to normalize and map relationships but I'm not sure you're doing it the right way. Get on AIM?
|
# ? Jul 21, 2013 19:41 |
|
When I'm done I'll convert this stuff to a csv, import it and replace the old values with new; once that's done I won't be using the spreadsheet for anything again. It's an unfortunate combination of bullshit software and a moron sending me poorly named files that need to made consistent with the rest of my stuff which hopefully won't occur again.
|
# ? Jul 21, 2013 21:10 |
|
I'm pretty sure I can make this much less of a pain in your rear end, but it's going to require some back and forth. Shoot me an IM and I'll help you out.
|
# ? Jul 22, 2013 00:45 |
|
Can anyone recommend resources for learning more about VSTO? I use VBA and you can do some neat stuff with it, but VSTO seems more versatile. And the JS stuff doesn't really seem ready for primetime yet; there are a handful of things you can't do easily with VBA in it, but it's missing huge swaths of functionality VBA does have.
|
# ? Jul 23, 2013 02:04 |
|
I have a column containing email domains. I want to isolate the TLD into a column (.com, .org, and so on). I normally do this with =mid(b2,find(".",b2)+1,3) but some of the domains have multiple periods, such as 104acv.skc.army.mil (we deal with government and military data). Another domain may only have one period like gmail.com, and another may have two periods, such as us.army.mil. Is there a way to find the last occurrence of the period, and copy out the characters following it?
|
# ? Jul 24, 2013 18:51 |
|
me your dad posted:I have a column containing email domains. I want to isolate the TLD into a column (.com, .org, and so on). This solution should work. http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba
|
# ? Jul 24, 2013 19:33 |
|
me your dad posted:I have a column containing email domains. I want to isolate the TLD into a column (.com, .org, and so on). If you can use VBA there's the "InStrRev" function to search from right to left. If you need to do cell formulas, I found this one on StackOverflow that uses substitution with '|' characters to find it. Works pretty well, I modified it to work with periods instead of spaces below: code:
|
# ? Jul 24, 2013 19:38 |
|
Thank you both - I'll give it a shot to see what I can make happen. Edit - The formula above worked perfectly - thanks again! me your dad fucked around with this message at 19:56 on Jul 24, 2013 |
# ? Jul 24, 2013 19:44 |
|
Couldn't you also have just taken the last 3 characters? Something like =SUBSTITUTE(RIGHT(TRIM(A1),3),".","")
|
# ? Jul 26, 2013 18:54 |
|
So long as it wasn't some two-letter code. But you could follow that line of thought and do some string-fu to find the number of characters that come after the last "." and return them.
|
# ? Jul 26, 2013 18:59 |
|
I think it makes it much easier to just keep a UDF with regexes on-hand to solve string manipulation problems. Something to the effect of thiscode:
But then, for instance, if I wanted the domain of an e-mail address, I could do something like =RegExReplace(RC[-3], ".*\.(\w+)\s*$", "$1") I have another one that uses the VBA split function, which is also pretty handy. I'm actually curious if anyone else has any really useful general-purpose UDFs. RICHUNCLEPENNYBAGS fucked around with this message at 02:35 on Jul 27, 2013 |
# ? Jul 27, 2013 02:30 |
|
Anyone know of any good guides on PowerPivot? Like starting from the basics.
|
# ? Jul 27, 2013 11:43 |
|
RICHUNCLEPENNYBAGS posted:I'm actually curious if anyone else has any really useful general-purpose UDFs. I had a great grab bag of stored procedures, but I just reinstalled Windows on my work PC and forgot to back up personal.xlsx So now I'm looking for more of these because I don't want to rewrite all that stuff. Don't be like me! Make backups! My first order of business will be to write a macro that exports all the modules in personal.xlsx to .vb files in a github repo. Some other good ones I had that I need to rewrite:
I can post these as I get them working again, or if anybody knows where similar objects have been published online I'd be grateful to be saved the assache.
|
# ? Aug 1, 2013 17:30 |
|
One down...code:
celestial teapot fucked around with this message at 00:29 on Aug 2, 2013 |
# ? Aug 1, 2013 18:49 |
|
Requiem posted:Perfect, friends--it works. Hi again, folks. I'm stumped again trying to combine ISBLANK and CONCATENATE. I find I'm smart enough to regurgitate your help (from June, p. 25 of this thread) but not to troubleshoot when I'm altering the context. Sorry... Here's the formula I currently have: code:
quote:Scott But I want unanswered questions hidden for each person. I WANT it to look like this: quote:Scott Can anyone help? Sorry to be so dense... EDIT: Happy to offer $20 via PayPal to whomever can help...
|
# ? Aug 2, 2013 22:17 |
|
Why can't you do something like: CONCATENATE(A1, IF(ISBLANK(A2), "", "Foo: " & A2)) I might consider using line breaks to make this less confusing... also perhaps & instead of the concatenate function. celestial teapot posted:UnMergeFill() RICHUNCLEPENNYBAGS fucked around with this message at 02:28 on Aug 3, 2013 |
# ? Aug 3, 2013 02:22 |
|
Requiem posted:Hi again, folks. I'm stumped again trying to combine ISBLANK and CONCATENATE. I find I'm smart enough to regurgitate your help (from June, p. 25 of this thread) but not to troubleshoot when I'm altering the context. Sorry... E:fb. This is notably filthy and there may be a cleaner way to do it, but the brute force approach looks like this: code:
You were clearly on a workable track with ISBLANK. You just need to get the if functions in order. Zorak of Michigan fucked around with this message at 02:48 on Aug 3, 2013 |
# ? Aug 3, 2013 02:45 |
|
That VBA would probably look something like this:code:
RICHUNCLEPENNYBAGS fucked around with this message at 03:47 on Aug 3, 2013 |
# ? Aug 3, 2013 03:37 |
|
Zorak of Michigan posted:E:fb. Zorak, thanks very much. Your code worked perfectly--and all I had to do was cut and paste, which for a guy who can see but not understand is a perfect solution! PM me your PayPal info please? With many thanks.
|
# ? Aug 3, 2013 20:21 |
|
Thanks but there's no need to pay me for that load of mess. I'm just addicted to problem solving. Also RICHUNCLEPENNYBAGS got the explanation out first. Unless you just don't give a crap, there's no reason to come away not understanding how this works. IF() has three sections- the logical test, the value for the cell if the test evaluates true, and the value if the test evaluates false. You already figured out that the right test for this is ISBLANK(), so you have to start with code:
code:
code:
code:
|
# ? Aug 4, 2013 02:39 |
|
Zorak of Michigan posted:Thanks but there's no need to pay me for that load of mess. I'm just addicted to problem solving. Also RICHUNCLEPENNYBAGS got the explanation out first. You should make a living doing this. Thanks for the straightforward explanations. I admit I was stumped at & replacing CONCATENATE--it seemed like darkest of magic to me. Thank you!
|
# ? Aug 4, 2013 05:40 |
|
Edit: Reworked, see below
celestial teapot fucked around with this message at 19:22 on Aug 7, 2013 |
# ? Aug 6, 2013 17:54 |
|
I lifted this RegEx replace module off some blog post, haven't tested it but it looks solidcode:
|
# ? Aug 6, 2013 18:24 |
|
Did this one the other day, don't think I posted it yetcode:
celestial teapot fucked around with this message at 19:55 on Aug 6, 2013 |
# ? Aug 6, 2013 18:25 |
|
I reworked the functions for counting distinct values by splitting them into separate functions using arrays.code:
code:
Let me know if I should keep posting these. celestial teapot fucked around with this message at 20:53 on Aug 7, 2013 |
# ? Aug 7, 2013 20:50 |
|
So my office recently upgraded from MS Office 2003 to Office 2010. Most people there typically create tables in Excel and paste as a picture (enhanced metafile) into PowerPoint. When you do that using Office 2010 it copies the default gridlines and the red comment triangle and shows them when you paste the table. To make it look proper I need to remove gridlines and make sure the comments aren't being shown before I copy, which is annoying. Is there any way copy and paste it as it show up if printed (i.e. no default gridlines, no comment or error triangles)?
|
# ? Aug 8, 2013 03:26 |
|
To remove gridlines, fill the cell color as white and clear any borders that may have been applied to the cell styles. To remove error flags, holds the answer to this question. I'm not familiar with power point but I am sure there is a better way to import your data into it.
|
# ? Aug 8, 2013 16:49 |
|
celestial teapot posted:To remove gridlines, fill the cell color as white and clear any borders that may have been applied to the cell styles. Well obviously. Those were the solutions that I posted to my own question. I just don't want to take an extra 15 seconds to hide grids and flags, copy and paste, and then spend another 15 seconds turning them back on. I was asking if there is a better way to import.
|
# ? Aug 8, 2013 21:54 |
|
|
# ? May 13, 2024 10:20 |
|
If filling the cell with a background color is then any other excel-based solution (eg VBA) is going to be a lot more work than that.
|
# ? Aug 8, 2013 21:56 |