Two Plus Two Publishing LLC
Two Plus Two Publishing LLC
 

Go Back   Two Plus Two Poker Forums > >

Notices

Other Other Topics Discussion of arts & entertainment, pop culture, food & drink, health and exercise, fashion, relationships, work, and just about anything else in life except poker, sports, religion and politics.

Reply
 
Thread Tools Display Modes
Old 03-09-2018, 05:47 PM   #2826
luvinurmoney
veteran
 
luvinurmoney's Avatar
 
Join Date: Jun 2010
Posts: 2,129
Re: Ask me anything about Microsoft Excel

Hello,

I was wondering about improving my spreadsheet to keep track of results. So as of right now I just calculate time start, time end. The equation I use is =IF(C2<B2,C2+1,C2)-B2. B2= time start C2= time stopped, D2=total time played. And also BR start BR end for profit. I would like to calculate total hours per day/week/month.

Also is it possible to speed up or automate any of the entries, like for example I'm always entering the BR ended into new BR started. Any tips and help highly appreciated.
luvinurmoney is offline   Reply With Quote
Old 03-11-2018, 10:04 PM   #2827
Phresh
Carpal \'Tunnel
 
Phresh's Avatar
 
Join Date: May 2006
Location: I got that snow... man.
Posts: 9,833
Re: Ask me anything about Microsoft Excel

That'd be pretty easy, but depends on exactly what you want and how it should look. If the bankroll isn't changing between sessions, just reference the last entry in the new cell. You can build a line chart in another worksheet as well to show your results as well.
Phresh is offline   Reply With Quote
Old 03-12-2018, 04:37 PM   #2828
luvinurmoney
veteran
 
luvinurmoney's Avatar
 
Join Date: Jun 2010
Posts: 2,129
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by Phresh View Post
That'd be pretty easy, but depends on exactly what you want and how it should look. If the bankroll isn't changing between sessions, just reference the last entry in the new cell. You can build a line chart in another worksheet as well to show your results as well.
Yea one of my friends basically told me for BR to just set the new = to the last cell.

https://gyazo.com/be93b29864a128d8683b469d94f6a37d

This is how I have it set up at the moment. What I would like to do is to be able to set it up where I can see the total hours and profit for the day/week/month. Similar to how you would be able to see it in HM2. I have been having trouble getting hhs from ignition so I keep track of profits manually. Ignition starting/ending is just basically BR.

As of right now, I just basically have a separate entry for each session. So right now what I'm having trouble with is figuring out exactly how to show the data I want to see and exact formulas to use. I think I have to use IF function but to be honest I can't seem to figure it out so I'm hoping someone more experienced and knowledgeable with excel can give me some advice.

I wouldn't mind paying someone to set up a nice spreadsheet for me (long as it's within reason). I think for someone knowledgeable and who knows what they are doing, it shouldn't take very long. It's just been way too long since i took an excel course and I don't remember anything haha.

PM me if interested please .
luvinurmoney is offline   Reply With Quote
Old 04-20-2018, 12:48 AM   #2829
ffr
adept
 
Join Date: Nov 2006
Posts: 1,185
Re: Ask me anything about Microsoft Excel

I'm having a hard time laying out a spreadsheet in a way I can see and compare some items.

These are the items I need to include in my spreadsheet:
Video Title
Date uploaded
Current Date
Days since uploaded
Total views
Total downloads
Today's views
Today's downloads

I just need help laying this information out so I can easily add the new numbers each day. I also need to see how these videos are performing each day as they get older and older from the original upload date.

I have several videos I need to track so the other challenge is that I'd like to have the data for each video next to each other (or right above/below each other) so I can see how one video is doing compared to others.

Anyone have any idea how to lay this information out?
ffr is offline   Reply With Quote
Old 05-04-2018, 01:30 PM   #2830
ffr
adept
 
Join Date: Nov 2006
Posts: 1,185
Re: Ask me anything about Microsoft Excel

I have a spreadsheet with a formula finding the days between some dates. How can I show a blank in the cell with the formula if one fo the dates has not been entered yet?

I have a date in cell B6 and another date in U6. I have a formula in cell V5 of: =U6-B6

I have this formula copied down the entire column of V. Some dates have not been entered in columnn U yet so the results that show in column V are all wacked until I enter a date in column U.

How can I change the formula in column V so it will remain blank if nothing is entered into column U?

For example, if I have a date entered in B23, but no date entered into U23, I'd like V23 to remain blank.


Thanks in advance!
ffr is offline   Reply With Quote
Old 05-04-2018, 04:47 PM   #2831
brrrrr
Pooh-Bah
 
Join Date: Dec 2009
Location: double
Posts: 4,336
Re: Ask me anything about Microsoft Excel

if(U23="","",U23-B23)
brrrrr is offline   Reply With Quote
Old 05-10-2018, 03:47 PM   #2832
CheckCheckFold
adept
 
CheckCheckFold's Avatar
 
Join Date: Sep 2004
Location: Bay Area, CA
Posts: 1,026
Re: Ask me anything about Microsoft Excel

I'm creating a pivot report using OLAP (the fact that it is OLAP probably shouldn't matter).

In the Filters section, I have Part Number
In the Rows section, I have Industry
In the Values section, I have Revenue

The Part Number filter is currently set to only show PartNumberA.

I want to have the report show the Revenue for PartNumberA vs all part numbers side by side without having to pull the Part Number filter down into a column or row since it explodes the chart. I just want to show 3 columns like below:

Industry : Revenue for Part A: Revenue for all Parts

IndustryA 50000 99383333
IndustryB 60000 45346666
IndustryC 70000 45466645

Can you help?
CheckCheckFold is offline   Reply With Quote
Old 05-10-2018, 06:55 PM   #2833
tireuw
centurion
 
tireuw's Avatar
 
Join Date: Mar 2007
Posts: 160
Re: Ask me anything about Microsoft Excel

In Alalyze-->OLAP Tools you can create your own measure using MDX. So you could create a measure for Revenue where Part Number = A and a separate measure for Revenue where Part Number =/= A. I don't know the MDX code off the top of my head though.

In a standard pivot you could group all the other parts, but I don't think an OLAP connection will let you. I think the only way to group if it's an OLAP connection is to create a hierarchy in the backend.
tireuw is offline   Reply With Quote
Old 05-14-2018, 04:34 PM   #2834
UbinTook
veteran
 
UbinTook's Avatar
 
Join Date: Jan 2007
Posts: 2,730
Re: Ask me anything about Microsoft Excel

Quote:
Originally Posted by UbinTook View Post
Maybe someone can direct me on how to create this:

I have zero experience with Excel( if someone wants to knock this out i would be eternally grateful)

My wife has an Extensive inventory of high end beads she need to inventory.

each page will have a header that she can type a generalized description:

"Loom beads, seed size 8" for example (Left justified but add about 10 spaces from left margin)

Each page will have 3 columns
the top of the following page will be a continuation of the bottom of the previous page.

(ignore dashes this is the only way i could get it to format here)
Quantity ------------Color Number----------Description
0-999 ----------------8 alphanumeric---------Typed description


The "color number" (column 2) needs to control sorting, BUT the whole line to include columns 1(quantity) and 3 (description)for that line need to move with it .

The "color numbers" are in a number of formats
Numerical
34
Alpha Numerical in various forms:
X345
XX345
3456X
X34567X

She would like to sort first by the first numeral in the item number
then start to consider the Alpha in this order
345
345X
X345
XX345
X345X


Column 1 needs to accommodate 3 digits at most (0-999)
Column 2 needs to accommodate 8 digits
Column 3 can be set to be as wide as necessary to take up the rest of the space on that side of the page for description
there should be delineation for each line, but not each column (like lined paper)
_________________________________________________
_________________________________________________
but data in each column should be (invisibly)justified to the left in that column they all start "In Line"

She needs to be able to add a line ( a new addition to the inventory and insert it wherever she needs to and have the spreadsheet re-sort accordingly).

Finally these need to print nicely so they are easy to reference.

I appreciate any advice or direction on how to accomplish this.
Quote:
Originally Posted by lkasigh View Post
Sorting is dead easy. You just select the columns you want to sort and use either the Sort or Filter function. It's very powerful and quite user friendly.

However your desired sort criteria are pretty convoluted compared to standard A-Z or High-Low. I'm sure it's technically possible, but it's going to be a challenge to implement. If possible, I would suggest adopting a new system of coding to conform to standard alphabetical sorting.

Alternatively, by using the Sort and Filter functions together, you may be able to achieve what you are looking for in another way. For instance, sort descending by quantity on hand, then filter for only the model #s that you are interested in.

Thank you for responding, i know i am late in my acknowledgement. Ill take a look at the suggestions you made, im not sure if there is a new system of coding as these are pre coded by the manufacturers...Its kind of a proprietary system to the industry. Changing coding would just cause mass confusion when comparing to other sort lists.
UbinTook is offline   Reply With Quote
Old 06-01-2018, 09:46 AM   #2835
erroneous
adept
 
erroneous's Avatar
 
Join Date: Nov 2006
Posts: 1,037
Re: Ask me anything about Microsoft Excel

I'm running into an issue and I can't figure out the problem. Each day I have to create a couple of rows of data based off certain information. Data includes words, numbers, and dates. Once I'm done with all the information, I have to save it in CSV (comma delimited format). All the cells are in the General format except for the dates which are in Short Date format.

Once the file is saved in CSV (comma delimited), I'll close it and re-open. Some of the numbers change. Example: In the sheet, I'll have three different rows in a column with the following numbers:

1056409843251
7853143251
9654343251

Once I save in CSV (comma delimited), close and re-open, the column now reads:

1056410000000
7853143251
9654343251

Not only has the top number changed, but it is also changed from General format to Scientific. Can someone tell me why it changed and how I can avoid this in the future? Thanks.
erroneous is offline   Reply With Quote
Old 06-01-2018, 06:51 PM   #2836
aditya
veteran
 
Join Date: Jun 2006
Location: GOAT
Posts: 2,908
Re: Ask me anything about Microsoft Excel

Its a common error where excel changes notation automatically to Scientific.

http://support.pitneybowes.com/Searc...lse&lang=en_US

See that link to see how to stop it (basically have to format as text to prevent it.)
aditya is offline   Reply With Quote
Old 06-03-2018, 01:46 AM   #2837
Steven_Q_Erkel
Pooh-Bah
 
Steven_Q_Erkel's Avatar
 
Join Date: Sep 2005
Location: Grooving with the eternal now.
Posts: 3,622
Re: Ask me anything about Microsoft Excel

Hello,

We have many clients that are us residents and require fifo gain/loss reporting when the firms resources are insufficient. How would you use excel to prep these reports given buy and sell transaction data assorted by settlement date? Can this be done via vba?
Steven_Q_Erkel is offline   Reply With Quote
Old 06-14-2018, 02:44 PM   #2838
ffr
adept
 
Join Date: Nov 2006
Posts: 1,185
Re: Ask me anything about Microsoft Excel

I want to add the word "Months" in the same cell as I have a calculation in. I tried to use:
=(H13/G19)&" Months"

This works, but the calculation I get has way too many decimal points. I tried to format so it only has two, but it still shows about 15.

The result I get now is:
3.213676384 Months

Is there a way to format to only two decimal points if I also want to have a word in the cell?

I'd like to show the result simply as:
3.21 Months

Thanks!
ffr is offline   Reply With Quote
Old 06-14-2018, 04:59 PM   #2839
LostOstrich
Carpal \'Tunnel
 
LostOstrich's Avatar
 
Join Date: Jan 2008
Location: Head in the sand
Posts: 15,535
Re: Ask me anything about Microsoft Excel

If the numerical value is in cell A1:

=CONCAT(ROUND(A1,2)," months")
LostOstrich is offline   Reply With Quote
Old 06-14-2018, 06:28 PM   #2840
Spurious
Carpal \'Tunnel
 
Spurious's Avatar
 
Join Date: Aug 2006
Location: Old Europe
Posts: 18,509
Re: Ask me anything about Microsoft Excel

You could also use the TEXT(value, format) formula.
Spurious is offline   Reply With Quote
Old 06-14-2018, 07:45 PM   #2841
ffr
adept
 
Join Date: Nov 2006
Posts: 1,185
Re: Ask me anything about Microsoft Excel

Sorry guys, I'm not that great with excel.

Right now, this is the exact formula I am using:
=(H13/G19)&" Months"

What should the exact formula be to use?
ffr is offline   Reply With Quote
Old 06-14-2018, 08:52 PM   #2842
aditya
veteran
 
Join Date: Jun 2006
Location: GOAT
Posts: 2,908
Re: Ask me anything about Microsoft Excel

=ROUND(H13/G19,0)&" months" if you want integer months

=ROUND(H13/G19,2)&" months" if you want decimal (ie 6.45) months
aditya is offline   Reply With Quote
Old 06-15-2018, 11:31 AM   #2843
ffr
adept
 
Join Date: Nov 2006
Posts: 1,185
Re: Ask me anything about Microsoft Excel

Perfect! Thank you aditya!!!
ffr is offline   Reply With Quote
Old 07-12-2018, 04:22 PM   #2844
topspinner
old hand
 
Join Date: May 2004
Posts: 1,310
Re: Ask me anything about Microsoft Excel

Thank you in advance for any help. I am wanting to download 6 different reports from https://baseballsavant.mlb.com/statcast_search and have them show up in 6 seperate sheets on a daily basis. The only thing that will change daily is the date range. Is there anyway to automate this using excel?
topspinner is offline   Reply With Quote
Old 07-19-2018, 06:11 PM   #2845
ffr
adept
 
Join Date: Nov 2006
Posts: 1,185
Re: Ask me anything about Microsoft Excel

I have a spreadsheet that doesn't look nice enough to show a potential customer. I need to clean it up and make it look more presentable.

Here's the data I have (with details removed




Any ideas to make it look more professional? How would you reformat the data? Would you add some type of chart to show the potential savings based on the three shipments areas they can save on? Any suggestions would be appreciated.


Thanks!!!
ffr is offline   Reply With Quote
Old 07-21-2018, 02:40 PM   #2846
Spurious
Carpal \'Tunnel
 
Spurious's Avatar
 
Join Date: Aug 2006
Location: Old Europe
Posts: 18,509
Re: Ask me anything about Microsoft Excel

I think this looks nice enough. Not a major fan of the color scheme but I am not an expert on this. Maybe show some consistency on the decimals for the percentages.

Surely, from the looks of it, this should be something the customer is excited about without having a nice chart or anything.
Spurious is offline   Reply With Quote
Old 07-22-2018, 09:42 PM   #2847
TheWhoWhat
centurion
 
Join Date: Sep 2014
Posts: 120
Re: Ask me anything about Microsoft Excel

Dumb Excel help needed. Doing a budget. Say the 12 months listed in the file is 100 per month

100 100 100 100 100 100 100 100 100 100 100 100 (Sum : 1200)

I realize by my research total tax should be 1500 for the year.

How can I write a formula to calculate the change in amount so I can do this for 2000+ cases. Keep in mind that the first 6 months of the year are locked (cannot change 1st six 100s).
TheWhoWhat is offline   Reply With Quote
Old 07-31-2018, 01:53 AM   #2848
ffr
adept
 
Join Date: Nov 2006
Posts: 1,185
Re: Ask me anything about Microsoft Excel

Is there a way to use conditional formatting (or something else) to highlight multiple cells if, and only if they all match. I can easily use conditional formatting to highlight a series of individual cells if they match, but not sure how to use that same idea to highlight a series of multiple cells if they match.

Example

Cell K5 =Tony
Cell L5 = Henderson
Cell M5 = Phoenix
(HIGHLIGHT)


Cell K6 =Tony
Cell L6 = Henderson
Cell M6 = San Diego
DO NOT HIGHLIGHT

Cell K7 =Tony
Cell L7 = Williams
Cell M7 = San Diego
DO NOT HIGHLIGHT

Cell K8 =Tony
Cell L8 = Henderson
Cell M8 = Phoenix
(HIGHLIGHT)


Cell K9 =Tony
Cell L9 = Williams
Cell M9 = Phoenix
DO NOT HIGHLIGHT


I'd like to only highlight the series of cells when all three match exactly. Would there be an easy way to go about doing this?


Thanks all!!!
ffr is offline   Reply With Quote
Old 09-27-2018, 11:45 AM   #2849
ffr
adept
 
Join Date: Nov 2006
Posts: 1,185
Re: Ask me anything about Microsoft Excel

I'm trying to count a group of cells (not in a continuous range) that contain a value greater than 0.

I'm trying to use the COUNTIF function.

The problem I'm having is that the group of cells are all in random places on my spreadsheet. I've tried to separate each cell with a comma, but that didn't work.

How can I include a random group of cells in a COUNTIF function? Or would there be a better function to use for this purpose?

I'd like to count all cells that are greater than 0 from this group of cells:

C5
C11
H29
M41
etc......


What formula would you use to count all these cells that are greater than 0?

Thanks!!!
ffr is offline   Reply With Quote
Old 09-27-2018, 06:29 PM   #2850
aditya
veteran
 
Join Date: Jun 2006
Location: GOAT
Posts: 2,908
Re: Ask me anything about Microsoft Excel

=SUM(COUNTIF(INDIRECT({"K21","K25","M24","O22","P2 8","Q24","S24"}),">"&0))
aditya is offline   Reply With Quote

Reply
      

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Forum Jump


All times are GMT -4. The time now is 09:43 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright © 2008-2017, Two Plus Two Interactive
 
 
Poker Players - Streaming Live Online