What's new
Fantasy Football - Footballguys Forums

Welcome to Our Forums. Once you've registered and logged in, you're primed to talk football, among other topics, with the sharpest and most experienced fantasy players on the internet.

***Official*** Excel Help Corner (1 Viewer)

Chemical X said:
well, each choice has a probability.

ie - Ned = 20%

        Chem X = 15%

so if both are chosen, that cell would be 35%.  but likely only 1 choice will ever be chosen.
Click to expand...


Chemical X said:
Dim Oldvalue As String

Dim Newvalue As String

[SIZE=12pt]Dim ProbVal, DDLoc
Dim DDrng As Range
Dim ProbRng As Range[/SIZE]

Application.EnableEvents = True

On Error GoTo Exitsub

If Target.Column = 16 Or Target.Column = 17 Or Target.Column = 18 Then

  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then

    GoTo Exitsub

  Else: If Target.Value = "" Then GoTo Exitsub Else

    Application.EnableEvents = False

    Newvalue = Target.Value

    Set DDrng = ActiveCell.Parent.Range(ActiveCell.Validation.Formula1)
    DDLoc = Application.WorksheetFunction.Match(ActiveCell.Value2, DDrng, 0)
    Set ProbRng = DDrng.Offset(0, 1)
    ProbVal = ProbRng(DDLoc)
    Application.Undo

    Oldvalue = Target.Value

      If Oldvalue = "" Then

        Target.Value = Newvalue

[SIZE=12pt]        Range("AA1").Value = ProbVal[/SIZE]

      Else

        If InStr(1, Oldvalue, Newvalue) = 0 Then

            Target.Value = Oldvalue & ", " & Newvalue

[SIZE=12pt]            Range("AA1").Value = Range("AA1").Value + ProbVal[/SIZE]

      Else:

        Target.Value = Oldvalue

      End If

    End If

  End If

End If

Application.EnableEvents = True

Exitsub:

[SIZE=12pt]On Error GoTo 0[/SIZE]

Application.EnableEvents = True

End Sub
Click to expand...
Assumption: the probabilities are in the column next to your list of dropdown values.

The code in orange will give you the corresponding probability for every selection chosen in the dropdown.  If more than 1 value is selected, it will add the probabilities.  Without knowing where you want the probability to be displayed, I stuffed it into cell AA1.  Change the reference to wherever you want it.

The code you posted has a bug - if an error is encountered, the code won't execute again when the dropdown is changed.  The error handling needs to be reset to 0 in - add the green code to fix the bug.

 
Ned said:
Assumption: the probabilities are in the column next to your list of dropdown values.

The code in orange will give you the corresponding probability for every selection chosen in the dropdown.  If more than 1 value is selected, it will add the probabilities.  Without knowing where you want the probability to be displayed, I stuffed it into cell AA1.  Change the reference to wherever you want it.

The code you posted has a bug - if an error is encountered, the code won't execute again when the dropdown is changed.  The error handling needs to be reset to 0 in - add the green code to fix the bug.
Click to expand...
will test this evening.  much thx

 
So I have a file that is 13,500 rows, for a government filing I have to make I only need to provide the government every 40th line from my data, if I start at row 2 outside of manually determining the 40th row is there an equation that move 40 rows don then possibly enter an asterisk so I can then sort and have my sample to supply? 

 
Fantasy_Freak said:
So I have a file that is 13,500 rows, for a government filing I have to make I only need to provide the government every 40th line from my data, if I start at row 2 outside of manually determining the 40th row is there an equation that move 40 rows don then possibly enter an asterisk so I can then sort and have my sample to supply? 
Click to expand...
=IF(MOD(ROW(),40)=0,"*","")

 
I have the current formula that is lookup of Column A11 on the Customers Tab to Column A of the Log Tab. When it matches, and Column C = "High Incident", the result is a numeric count of the number of occurances:

=COUNTIFS(Log!$A:$A,'Customers'!$A11,Log!C:C,"High Incident")

I need to add an additional validation to the above formula. When the customer tab and the Log tab matches, and Column C = "High Incident",  add this check: Column H in the Log Tab must be = 'BBB'

How would I add to that to the existing formula?

 
cubd8 said:
I have the current formula that is lookup of Column A11 on the Customers Tab to Column A of the Log Tab. When it matches, and Column C = "High Incident", the result is a numeric count of the number of occurances:

=COUNTIFS(Log!$A:$A,'Customers'!$A11,Log!C:C,"High Incident")

I need to add an additional validation to the above formula. When the customer tab and the Log tab matches, and Column C = "High Incident",  add this check: Column H in the Log Tab must be = 'BBB'

How would I add to that to the existing formula?
Click to expand...
Don't take this the wrong way, but I'd expect you to know how to add a 3rd set of criteria in COUNTIFS if you've already did 2 of them in the original. :unsure:

You can keep repeating the below for over 100 different sets of criteria.  The first argument is the lookup range, the 2nd being the criteria for the lookup.  Rinse/repeat...

=COUNTIFS(Log!$A:$A,'Customers'!$A11,Log!C:C,"High Incident",Log!$H:$H,"BBB")

 
Ned said:
Don't take this the wrong way, but I'd expect you to know how to add a 3rd set of criteria in COUNTIFS if you've already did 2 of them in the original. :unsure:

You can keep repeating the below for over 100 different sets of criteria.  The first argument is the lookup range, the 2nd being the criteria for the lookup.  Rinse/repeat...

=COUNTIFS(Log!$A:$A,'Customers'!$A11,Log!C:C,"High Incident",Log!$H:$H,"BBB")
Click to expand...
Thank you, I had logic that was similar, but starting adding to it, and got lost. I'm definitely now that skilled in writing some of these formula's.

 
One other question.

When I sort my data, the formula's change and the result is the data is not sorted correctly.

Example of formula's. 

Is there a trick to keeping my formula's intact when sorting?

=COUNTIFS(Log!$A:$A,'BCS Customers'!$A6,Log!$I:$I,"BCS",Log!D:D,"High Incident")

=COUNTIFS(Log!$A:$A,'BCS Customers'!$A6,Log!$I:$I,"BCS",Log!D:D,"Incident")

=COUNTIFS(Log!$A:$A,'BCS Customers'!$A6,Log!$D:$D,"Incident",Log!$I:$I,"BCS",Log!$E:$E,">=" &EOMONTH(TODAY(),-3)+1,Log!$E:$E,"<=" &EOMONTH(TODAY(),0))

 
Why are you sorting your formulas?  Is there any reason that the formulas should be rearranged when you sort the data?

 
Looking for excel formula help.  I have data I need to match in two different sources and one of the keys I'll be matching on is customer name.  The format in one system is last name, first name.  the format in the other system is last name ,first name.  The difference is in the placement of the comma - either a space before the comma, or a space after the comma.  How in excel can I go into a cell and change the spacing so that it is before the comma instead of after?

example:  change cell A1 from John, Doe to John ,Doe

 
coyote5 said:
Looking for excel formula help.  I have data I need to match in two different sources and one of the keys I'll be matching on is customer name.  The format in one system is last name, first name.  the format in the other system is last name ,first name.  The difference is in the placement of the comma - either a space before the comma, or a space after the comma.  How in excel can I go into a cell and change the spacing so that it is before the comma instead of after?

example:  change cell A1 from John, Doe to John ,Doe
Click to expand...
Can't you just use SUBSTITUTE?  Try SUBSTITUTE(A1, ", ", " ,")

 
coyote5 said:
Looking for excel formula help.  I have data I need to match in two different sources and one of the keys I'll be matching on is customer name.  The format in one system is last name, first name.  the format in the other system is last name ,first name.  The difference is in the placement of the comma - either a space before the comma, or a space after the comma.  How in excel can I go into a cell and change the spacing so that it is before the comma instead of after?

example:  change cell A1 from John, Doe to John ,Doe
Click to expand...
If you really want to do it with formulas, put this in B1:

=LEFT(A1,FIND(",",A1)-1)&" ,"&MID(A1,FIND(",",A1)+2,LEN(A1)-FIND(",",A1)-1)

 
bryhamm said:
If you really want to do it with formulas, put this in B1:

=LEFT(A1,FIND(",",A1)-1)&" ,"&MID(A1,FIND(",",A1)+2,LEN(A1)-FIND(",",A1)-1)
Click to expand...
LOL no, I'm looking for the easiest way, and it looks like Walking Boot's Replace suggestion will be a winner :thumbup:

 
Does anyone else also hate the way charts work on 2016 vs. 2013?

I was very comfortable with using 2013 to customize chart appearances, 2016 sucks.

I have a clustered column chart I created in 2013.

When I try to update it by selecting different cells as the data in 2016 using the same data selection method I used when I created it, it stacks the two columns like a stacked column chart, and changes the formatting I had for the data series to something horrible to look at.

If I swap the chart type to 3D clustered column the columns appear fine side by side and only destroys the formatting minimally. 

It still works fine if I use someone else's 2013 to change the chart using the original clustered column or going 3D.. 

 
I have a pivot table problem with the 2nd formula below, and it's hard to provide an example so I'll do my best to explain:

First formula in my main tab:

My first formula is working great. I am matching the value found in Column A8 of my main tab to a tab called 'Turntime' with this formula:

=IF(ISNA(VLOOKUP(A8,Turntime!A:B,2,0)),0,VLOOKUP(A8,Turntime!A:B,2,0))

This formula will include all possibilities (approximately 100) so that when each value matches, I will return the value found in Column C.

Second Formula:

I'm not getting my expected results with this formula:

I am attempting to match the names from my main tab (about 100 names in Column A) to my Turntime tab in Column E of about 50 names. What am I seeing is that when my main table doesn't match the value found in Turntime, I am not outputting the expected value.

Example: 

Column A of main tab = CustomerA
Column E of Turntime Tab = CustomerA 
Result of this match should return the value found in Column F, else a zero.

This is my current formula:

=IF(ISNA(VLOOKUP($A8,Turntime!A:F,6,0)),0,VLOOKUP($A8,Turntime!A:F,6,0))


Main Tab:
Column A:
CustomerA
CustomerB
....

Turntime Tab:
Column A:       Column B:     Column C:     Column D:       Column E:      Column F:        Column G:
CustomerA             13               78                                       CustomerA         10                    0

 
Are column A and column E of the Turntime tab always the same? I ask because in your description of what you are expecting for your second lookup, you mention matching to the value in column E, but in your formula, you are using column A for the match to pull column F, not column E.

 
cubd8 said:
I have a pivot table problem with the 2nd formula below, and it's hard to provide an example so I'll do my best to explain:

First formula in my main tab:

My first formula is working great. I am matching the value found in Column A8 of my main tab to a tab called 'Turntime' with this formula:

=IF(ISNA(VLOOKUP(A8,Turntime!A:B,2,0)),0,VLOOKUP(A8,Turntime!A:B,2,0))

This formula will include all possibilities (approximately 100) so that when each value matches, I will return the value found in Column C.

Second Formula:

I'm not getting my expected results with this formula:

I am attempting to match the names from my main tab (about 100 names in Column A) to my Turntime tab in Column E of about 50 names. What am I seeing is that when my main table doesn't match the value found in Turntime, I am not outputting the expected value.

Example: 

Column A of main tab = CustomerA
Column E of Turntime Tab = CustomerA 
Result of this match should return the value found in Column F, else a zero.

This is my current formula:

=IF(ISNA(VLOOKUP($A8,Turntime!A:F,6,0)),0,VLOOKUP($A8,Turntime!A:F,6,0))


Main Tab:
Column A:
CustomerA
CustomerB
....

Turntime Tab:
Column A:       Column B:     Column C:     Column D:       Column E:      Column F:        Column G:
CustomerA             13               78                                       CustomerA         10                    0
Click to expand...
What is your expected value?  The bold is exactly what it should be doing based on my review of your formula.

Are you sure that you don't have any trailing spaces in your "CustomerA"?

Also, you can simplify the syntax by doing using IFERROR: "=IFERROR(VLOOKUP($A8,Turntime!A:F,6,0),0)"

 
why the mother#### does excel sometimes add .00 at the end of data?

i've got a whole column of data that reads 07/10/2017.00

:hot:

 
mr. furley said:
why the mother#### does excel sometimes add .00 at the end of data?

i've got a whole column of data that reads 07/10/2017.00

:hot:
Click to expand...
Generally, it's just because the cell/column is formatted to display it that way. Click on the cell, and look in the formula bar to see the actual data that lives there, without the formatting.

 
def looks like a custom format  with the slashes and .00  hard coded 

right-click one of the cells, and hit Format and see what it shows there

 
@furley highlight the entire worksheet and hit ALT+H+E+F. should clear out all formats, unless you've got some inception level formatting going on

 
Last edited by a moderator:
Walking Boot said:
When you right click one of those weird cells, and hit Format Cell..., what shows up in the format option? It should just be Date mm/dd/yy, and not anything custom (like, mm/dd/yyyy.## or something stupid like that)

I mean, that format's just so weird you should probably sacrifice a chicken and start over from scratch.
Click to expand...
it does just show standard mm/dd/yy

but no matter what i do, when pasting the data it adds .00 to the date column :hot:

the source i'm copying from is not a Microsoft product. this has come up before when copying from the same platform and pasting to Excel. it has to be in the source info. but i can't change that. 

stupid effing computers :hot:

 
mr. furley said:
it does just show standard mm/dd/yy

but no matter what i do, when pasting the data it adds .00 to the date column :hot:

the source i'm copying from is not a Microsoft product. this has come up before when copying from the same platform and pasting to Excel. it has to be in the source info. but i can't change that. 

stupid effing computers :hot:
Click to expand...
Have you tried pasting as values to see if that makes a difference?

 
GregR said:
Sometimes it can also help to paste to something simple like Notepad that will drop formatting. And if it looks ok there, then copy it from there to paste into Excel.
Click to expand...
just tried it

"07/10/2017"    11.340000    07/11/2017.00 :hot:   2067.000    

 
Peyton Marino said:
Can you share the source for your data so we can see what you're looking at?
Click to expand...
nope. it's a proprietary program & data. 

i just did text to columns to remove the .00 from date. simple enough.. but wtf does it keep pasting that way?

i've run this same process, to pull different data (including the same date field) thousands of times and maybe... 2 or 3 times previously this has happened. no one here can figure it out. been in contact with the group that supports the product i'm pulling data from and they can't figure it either.

 
mr. furley said:
nope. it's a proprietary program & data. 

i just did text to columns to remove the .00 from date. simple enough.. but wtf does it keep pasting that way?

i've run this same process, to pull different data (including the same date field) thousands of times and maybe... 2 or 3 times previously this has happened. no one here can figure it out. been in contact with the group that supports the product i'm pulling data from and they can't figure it either.
Click to expand...
What is the format of the cell before you paste the date into it?  Try making the empty cell text format, then paste the date into it.  You can see if there are any extraneous digits at the end of what you pasted into the cell.  Then change the format to a date format and see if that works.  I tried this by copying a date from the FBG website (Ezekiel Elliott's birthday), and I swear there was a space at the end of what I pasted.  Maybe some strange character is getting pasted into the cell along with the date.

Also, if you change the format of the cell after pasting the date into the cell, what do you see?  If excel is really storing the pasted characters as a date, you should see a value of 42926 representing 7/10/2017.

 
To quickly sum columns, I use a keyboard shortcut of Alt + "equal sign" (I believe this is a default shortcut, it's not something I created or set up).  It automatically creates the sum formula with the column information pre-populated, so you just have to hit enter.

However, I did something where Alt + "equal sign" now does the subtotal function instead of the sum function. Any idea how to get it back to being sum instead of subtotal?

 
does anyone use scripting at work to do your job for you?

I recently started with autoit and have turned a normal 20 minute job into 2 minutes

 
shady inc said:
To quickly sum columns, I use a keyboard shortcut of Alt + "equal sign" (I believe this is a default shortcut, it's not something I created or set up).  It automatically creates the sum formula with the column information pre-populated, so you just have to hit enter.

However, I did something where Alt + "equal sign" now does the subtotal function instead of the sum function. Any idea how to get it back to being sum instead of subtotal?
Click to expand...
Is it just because you have grouping turned on perhaps? Excel doesn't let you customize their default keyboard shortcuts.

 
Nathan R. Jessep said:
Is it just because you have grouping turned on perhaps? Excel doesn't let you customize their default keyboard shortcuts.
Click to expand...
You were close, and led me to the answer - I filter out rows that have a zero balance before printing.  Anytime rows are filtered, it will subtotal.  If all rows are showing (filter turned off), it will sum.  Thanks for the help!

 
Walking Boot said:
So, basic question, how big are the files you guys are working on?

We're having major problems with our IT department with our computers. IT points the finger back at us, saying we're creating file too large for our computers to handle. I point out that some of the biggest ones top out at 1.5, maybe 2.0 MB (M, not G), and that should be fine for a computer with 8GB of RAM, but I get back "Well, for Excel, 2 megabytes is a big file."

To me, that's laughable, but... you tell me. Am I nuts?
Click to expand...
That's a loaded question.  If it's just 1.5-2mb of straight data in a spreadsheet stored on your local machine - meh, no biggie.

If it's that size and has a slew of nested formulas and is stored on a shared server - ehhhh, I can see that being a problem.

That's been my experience, but I should note that I'm a remote employee so accessing the shared servers is a little more of an issue vs. if I was in the office.

 
One of the main files that I use daily is about 20MB - mix of data, pivottables, some formulas and a couple charts.  Have no issues with it at all. Have definitely used and created some much larger files, and only time there are performance issues is when I've run into trying to do some serious calculations or array formulas with a very large data set.

 
Walking Boot said:
Another question:

I have a custom number format to display some data. It always displays as a four-part series of two digit numbers, separated with colons. ie: "00:00:00:00". Other examples might be "01:20:15:23" or "02:45:22:44".

Is there a simple way, besides nesting a bunch of LEFT and RIGHTs, to pull each two-digit number out and put it into different cells? So, if A1 is "02:45:22:44", what's the easiest-to-understand to a layperson formula to have A2 be "02", A3 be "45", A4 be "22", and A5 be "44"?
Click to expand...
If you could have the numbers be separated out across the columns, perhaps running text to columns with the colon as the delimiter would work? Not at a place to test it and to see if it would work based on a custom number format, as I'm assuming the custom number format is only displaying the colons, they aren't actually in your data.

 
acarey50 said:
If you could have the numbers be separated out across the columns, perhaps running text to columns with the colon as the delimiter would work? Not at a place to test it and to see if it would work based on a custom number format, as I'm assuming the custom number format is only displaying the colons, they aren't actually in your data.
Click to expand...
Yep, colon as the delimiter works fineif that's part of the actual data in the cells (put : in the "Other" delimiter box). Then if you need it as rows instead of columns, you can always copy and Paste Special and use the "Transpose" option to paste into rows, but that could get a little cumbersome

 
Walking Boot said:
So, basic question, how big are the files you guys are working on?

We're having major problems with our IT department with our computers. IT points the finger back at us, saying we're creating file too large for our computers to handle. I point out that some of the biggest ones top out at 1.5, maybe 2.0 MB (M, not G), and that should be fine for a computer with 8GB of RAM, but I get back "Well, for Excel, 2 megabytes is a big file."

To me, that's laughable, but... you tell me. Am I nuts?
Click to expand...
I have files I run at home that are between 10-20mb. Usually turning off automatic formula recalcing and not recalcing formulas when saving helps a ton. I think you should be fine, but what Ned says makes sense too.

 
You must log in or register to reply here.

Similar threads

ragincajun
Excel AI!!!!
Replies
26
Views
990
culdeus
culdeus
Max Power
32-Hour Work Week
2
Replies
70
Views
763
Desert_Power
Desert_Power
L
Work Efficiency & Organization
Replies
15
Views
559
MAC_32
MAC_32

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Top