Chemical X
Footballguy
the requisition is out there......this is what happens when you force someone to be a financial analyst when their role is not that.
Fantasy Football - Footballguys Forums
This is a sample guest message. Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!
***Official*** Excel Help Corner (1 Viewer)
- Thread starter Ned
- Start date
Peyton Marino
Footballguy
From a data visualization perspective, generally speaking pie charts are a very poor conveyor of information unless you are simply comparing two options, i.e. Yes/No responses, etc.Chemical X said:
Anything more than that, and you are usually much better served with column or bar charts.
What is it that you will be trying to show with the chart?
Ned
Footballguy
Agreed.
Also the data needs to be laid out correctly for charts to work properly. I've seen too many folks pull their hair out trying to make a chart only because their data is crap to begin with.
Ned
Footballguy
munga30
Checkin' my email
I have a list of items ~16000 long. I'd like to insert some kind of control for jumping down the list. If I type 0.5%, I'm jumped to a cell very close to the top of the list. 45% is near the middle, 90% is near the bottom etc. ActiveX scroll bars seem like they could do this but I can't get it to work. Ultimately I'd like an object that sits fixed within the excel window with the sheet sliding under it. Once I'm jumped to a spot on the list I'm interested in its neighbors. Want it sheet specific as I have other lists on different sheets in the same workbook. Worth DIYing this or is there a cheap outsourcing option?
Ned
Footballguy
I'm trying to match data from Sheet1 (Column H) to Sheet2 (Column A).
When the values match, output the value in Column K (from Sheet2) to Column M of Sheet1.
If the values do not match, output a '0'.
How do I go about doing this? My current formula isn't outputting.
Thank You!
When the values match, output the value in Column K (from Sheet2) to Column M of Sheet1.
If the values do not match, output a '0'.
How do I go about doing this? My current formula isn't outputting.
Thank You!
Ned
Footballguy
I could cook something up for you, but having a sample sheet that you're working with would go a long way.
Ned
Footballguy
Assuming you're going against same row in all sheets? Put this in cell M1. Writing this from iPhone, so no guarantee it's error free!
=IF(Sheet1!H1=Sheet2!A1,Sheet2!K1,0)
If not going against the same rows, but having unique values, could nest a vlookup inside an iserror inside of an if statement.
Something like =IF(ISERROR(VLOOKUP(Sheet1!H1,Sheet2A:K,11,FALSE)),0,(VLOOKUP(Sheet1!H1,Sheet2A:K,11,FALSE))
Not able to test it just now, but the way it works is that it runs the vlookup and if it doesn't find a match, it returns a zero, and if it does find a match, it returns the result of the vlookup, which is column K from Sheet 2. You would put this formula into column M
Ned
Footballguy
dal_boys_phan
Footballguy
Can't you just F5 and pick the cell you would want to jump to? So, if you want to go 75% of the way down the list, F5, type A12000 and be where you want to be? Probably not the solution you are looking for if you are wrestling with Active X
If you want the row of precise, yet not simple percentages (say, which row is 19.53% of the way to the end of the list), you could put a row at the top with three cells - (1) to count (using the =COUNT formula) the remaining rows (2) then a cell for you to enter the percentage and (3) multiply the two to get the row you would need to jump to for that precise percentage. And then lock this row at the top of the sheet. You may be able to also create a CNRL + function with a macro to run off the value you calculated in the third cell, but that's beyond my off the top of my head capabilities.
Last edited by a moderator:
Thanks this works well. One additional modification.
Current formula:
=IF(OR(H2="A",H2="B"),"standard",H2)
However, I need to this for a few other scenario's as well.
The current formula works fine for the 1st scenario but there are 3 other ELSE type conditions. How would I add in those?
=IF(OR(H2="A",H2="B"),"standard",H2)
ELSE
When H2 = X or Y, output moderate, H2
ELSE
When H2 = J or K, output complex, H2
ELSE
When H2 = M or N, output very complex, H2
Bottomfeeder Sports
Footballguy
You can nest IF functions. So you I assume you recognize that the four above would be-
=IF(OR(H2="A",H2="B"),"standard",H2)
=IF(OR(H2="X",HY="B"),"moderate",H2)
=IF(OR(H2="J",HY="K"),"complex",H2)
=IF(OR(H2="M",HY="N"),"very complex",H2)
Assuming you only want H2 when all of the test fail you would replace all but the last H2 in the false side of the IF with the next IF such as...
=IF(OR(H2="A",H2="B"),"standard",IF(OR(H2="X",HY="B"),"moderate",IF(OR(H2="J",HY="K"),"complex",IF(OR(H2="M",HY="N"),"very complex",H2))))
You could also consider a SWITCH function if the IFs get too cumbersome,
=SWITCH(h2, "X", "moderate", "Y", "moderate", "J"," complex", "K", "complex", "M", "very complex", "N", "very complex", "A", "standard", "B", "standard", h2)
And when that gets too cumbersome there are the lookups...
That is assuming that I understood the goal....
Ned
Footballguy
It's easy to get turned around when nesting; nice job explaining how to do it.
Niles Standish
Footballguy
So here's what I have:
On spreadsheet 1 I have A, B and C filled with values. The combination of A, B AND C should never have duplicates although A, B or C will often have duplicates if that makes sense. I also have D, E, F filled with different values. Those correspond to A, B and C as long as they're on the same row.
On spreadsheet 2 I have A, B, C filled with values (they match A, B, C on spreadsheet 1. But I do not have the corresponding D, E and F on that spreadsheet.
What I am trying to do is go to spreadsheet 2 and take A, B, C and fill in new rows D, E, F (with the values from spreadsheet 1).
If what I'm trying to do is clear (it might not be
) does anyone know a formula that can do what I want?
I might be able to do with an H Lookup but I'm not really familiar with how to do those with multiple columns.
On spreadsheet 1 I have A, B and C filled with values. The combination of A, B AND C should never have duplicates although A, B or C will often have duplicates if that makes sense. I also have D, E, F filled with different values. Those correspond to A, B and C as long as they're on the same row.
On spreadsheet 2 I have A, B, C filled with values (they match A, B, C on spreadsheet 1. But I do not have the corresponding D, E and F on that spreadsheet.
What I am trying to do is go to spreadsheet 2 and take A, B, C and fill in new rows D, E, F (with the values from spreadsheet 1).
If what I'm trying to do is clear (it might not be
) does anyone know a formula that can do what I want?I might be able to do with an H Lookup but I'm not really familiar with how to do those with multiple columns.
Ned
Footballguy
Use INDEX/MATCH.So here's what I have:
On spreadsheet 1 I have A, B and C filled with values. The combination of A, B AND C should never have duplicates although A, B or C will often have duplicates if that makes sense. I also have D, E, F filled with different values. Those correspond to A, B and C as long as they're on the same row.
On spreadsheet 2 I have A, B, C filled with values (they match A, B, C on spreadsheet 1. But I do not have the corresponding D, E and F on that spreadsheet.
What I am trying to do is go to spreadsheet 2 and take A, B, C and fill in new rows D, E, F (with the values from spreadsheet 1).
If what I'm trying to do is clear (it might not be
I might be able to do with an H Lookup but I'm not really familiar with how to do those with multiple columns.
You'll need to build a key in a helper column so you can perform the look-ups by combining the data in A/B/C on spreadsheet 1. So say in column G of spreadsheet 1, you put a formula "=A2&B2&C2". Autofill that all the way down. This will be a unique key since you said A, B, and C will never have dups.
With that key built in spreadsheet 1, you can now grab the data based on what's in A:C of both sheets. Go to your spreadsheet 2 and in column D, you put "=INDEX(spreadsheet1!D
,MATCH($A2&$B2&$C2,spreadsheet1!$G:$G,0),0)". Repeat for E and F and then autofill them all the way down. You'll need to obviously enter the correct spreadsheet names for the stuff in red.
hagmania
Footballguy
INDEX-MATCH is a good solution for text. I humbly offer if they are numerical values you can use SUMIFS.
=SUMIFS(spreadsheet1!D, spreadsheet1!$A:$A, $A2, spreadsheet1!$B:$B, $B2, spreadsheet1!$C:$C, $C2)
That goes in D2 and you can copy over and down through column F.
You can also INDEX-MATCH-MATCH and fill in as many columns as you'd like at once, so long as they contain the same exact headers. Modifying Ned's setup and formula above:
=INDEX(spreadsheet1!$A:$G,MATCH($A2&$B2&$C2,spreadsheet1!$G:$G,0),MATCH(D$1, spreadsheet1!$A$1:$G$1))
That goes in D2 and you can copy over and down through column F.
=SUMIFS(spreadsheet1!D
, spreadsheet1!$A:$A, $A2, spreadsheet1!$B:$B, $B2, spreadsheet1!$C:$C, $C2)That goes in D2 and you can copy over and down through column F.
You can also INDEX-MATCH-MATCH and fill in as many columns as you'd like at once, so long as they contain the same exact headers. Modifying Ned's setup and formula above:
=INDEX(spreadsheet1!$A:$G,MATCH($A2&$B2&$C2,spreadsheet1!$G:$G,0),MATCH(D$1, spreadsheet1!$A$1:$G$1))
That goes in D2 and you can copy over and down through column F.
Last edited by a moderator:
Niles Standish
Footballguy
Thank you both that got me just what I need. Now that my days work is over I can go take a nap. Maybe not but at least it helped me get through something on my to do list. 
Question:
H26 is a date field (ex. 01/13/2017)
AN26 is a date field (ex. 01/12/2017)
Here is my formula:
=IF(H26="","-1",IF(AN26="","-1",DATEDIF(AN26,H26,"D")))
In th example above, my forumula is returning a value of '1' but my formula below is returning a value of #NUM! in situations where H2 is an older date than AN.
Ex.
H27 = 12/08/2016 and AN27 = 01/11/2017 returns a #NUM!
H28 = 01/03/2017 and AN28 = 01/06/2017 returns a #NUM!
How do I fix this? Do I need a check that returns a '-1' (as a default) if Column H is older than AN? If so how do I go about doing this?
H26 is a date field (ex. 01/13/2017)
AN26 is a date field (ex. 01/12/2017)
Here is my formula:
=IF(H26="","-1",IF(AN26="","-1",DATEDIF(AN26,H26,"D")))
In th example above, my forumula is returning a value of '1' but my formula below is returning a value of #NUM! in situations where H2 is an older date than AN.
Ex.
H27 = 12/08/2016 and AN27 = 01/11/2017 returns a #NUM!
H28 = 01/03/2017 and AN28 = 01/06/2017 returns a #NUM!
How do I fix this? Do I need a check that returns a '-1' (as a default) if Column H is older than AN? If so how do I go about doing this?
If Date1 is later than Date2, DATEDIF will return a #NUM! error.
For it to work for you, I think you need to switch the formula to be =IF(H26="","-1",IF(AN26="","-1",DATEDIF(H26,AN26,"D")))
Alternatively, if the dates could be in varying order, ie sometimes AN is after H or vice versa, and you are always just looking to calculate the number of days, you could consider using the DAYS formula or even more simply just doing =AN26-H26 as your formula. Both of those will give you negative numbers as well if the first date is after the second date.
Thank you. One question.
If I do AN26-H26, how would I go about setting a default if the value returned is GT OR LT 99?
Probably have to do more nesting of IF statements.
Something like: =IF(H26="","-1",IF(AN26="","-1",IF(AN26-H26>99,"What you want to say for greater than 99",IF(AN26-H26<99,"What you want to say for less than 99",AN26-H26))))
With that formula, you will get "-1" if either H or AN is blank, get "What you want to say for greater than 99" if AN26-H26 is greater than 99, "What you want to say for less than 99" if AN26-H26 is less than 99 (this includes negative values, so if H26 is later than AN26, will get this value, and will get "99" if AN26 is exactly 99 days after H26 (can also adjust one of the greater or less than statements to include equal to if needed)
Chemical X
Footballguy
@Ned or other nerds, I have the simplest of questions.....
so, I have 2 columns A & B. on row 2 in column C I set a formula to calculate A2 - B2. when I copy the formula from C2 to C3, C4, C5, etc. the answer remains the answer for C2. the formula copies, but it copies the initial result. tried automatic, manual, text, etc.
any ideas? seems so simple......thx
so, I have 2 columns A & B. on row 2 in column C I set a formula to calculate A2 - B2. when I copy the formula from C2 to C3, C4, C5, etc. the answer remains the answer for C2. the formula copies, but it copies the initial result. tried automatic, manual, text, etc.
any ideas? seems so simple......thx
Last edited by a moderator:
Donkey Derp
Footballguy
Do you have a $ in front of the 2s? I.E. your formula is =A$2-B$2?
$ fixes a column or row even if you copy it elsewhere.
Donkey Derp
Footballguy
Also try hitting F9 to force Excel to calculate. See if that works.
Chemical X
Footballguy
new query.....
in a cell, "wife" has a drop down box of potential actions that can be selected. it only allows her to select 1 action from the dropdown. she'd like to select more than one.
tia
in a cell, "wife" has a drop down box of potential actions that can be selected. it only allows her to select 1 action from the dropdown. she'd like to select more than one.
tia
Last edited by a moderator:
To my knowledge, this can only be done using VBA code.
Here is one such online tutorial showing how to do this. Can do a google search for others, but almost all essentially do the same thing.
Chemical X
Footballguy
Pigskin Fanatic
Footballguy
If you're either not doing anything with the data selected (as in not passing selected values to some other program) or if you know what you're doing in VB, add a ListBox onto the Excel sheet (Developer --> Insert --> ListBox) instead of the dropdown or ComboBox and point it to the same data points (right click on the ListBox, select Format Control). You can click on multiple selections without code.
Last edited by a moderator:
Chemical X
Footballguy
new query;
so wife has a list of items that can be added via drop down in column A. each item has a % of probability associated with it. she would like that when an item is chosen for column A, the associated % populates automatically in column B.
so wife has a list of items that can be added via drop down in column A. each item has a % of probability associated with it. she would like that when an item is chosen for column A, the associated % populates automatically in column B.
Ned
Footballguy
You'll need to store a list of the dropdown values and their associated probabilities. Let's say they're in Sheet2: col A is dropdown values and col B is %.
Next to your dropdown column (shown as col Z here):
=INDEX(Sheet2!B:B,MATCH(Z1,Sheet2!A:A,0),0)
GregR
Footballguy
Does it have any dollar signs on it? Like: =$A$2-$B$2 or =A$2-$B$2 ?
If so remove the dollar signs, as they lock it into that exact row or column for times you want to copy the formula and keep it pointing where it did originally.
Edit: Whoops, didn't realize there was another page of the thread with this answered
Last edited by a moderator:
Chemical X
Footballguy
Ned
Footballguy
Chemical X
Footballguy
Ned
Footballguy
That's the kind of info we need. Most times when users say 'dropdown', they're talking about the data validation lists.
Did you author the VBA code? It should be simple to add another step in the code to add the corresponding probability.
Chemical X
Footballguy
Ned
Footballguy
Chemical X
Footballguy
Dim Oldvalue As String
Dim Newvalue As String
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
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Ned
Footballguy
Problem is going ot be that because you are able to put any combination of selections into column A, and they come in as a text string in the order they are selected, I think you are going to have a very difficult time coming up with a lookup that will be able to pull over the probabilities, as you would need to have a list with every possible combination of values in every possible order in order for it to work, as I would think that for items with multiple selections you would want some sum of the various probabilities.
Perhaps some code could be created to create a running sum of probabilities after each option that is selected, but that is beyond my capabilities.
Perhaps some code could be created to create a running sum of probabilities after each option that is selected, but that is beyond my capabilities.
Chemical X
Footballguy
Ned
Footballguy
Chemical X
Footballguy
When it comes to coding you have to treat anything that has a possibility of happening as if it's going to happen every time. Failing to do so gives you bugs.
Similar threads
