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 (4 Viewers)

here is a word question;

wife has column A which contains 35 rows of action items.  column B is to be blank or have a check mark/X in it based on whether or not the item in column A has been completed.

row 36 column B she would like to have automatically calculate the % of items checked/Xd of the 35 items.

i.e. - 10 items in column B are checked/Xd so row 36, column B should automatically populate as 28.5%.

any thoughts on how to get the formula for this into row 36, column B?

thx

 
Chemical X said:
here is a word question;

wife has column A which contains 35 rows of action items.  column B is to be blank or have a check mark/X in it based on whether or not the item in column A has been completed.

row 36 column B she would like to have automatically calculate the % of items checked/Xd of the 35 items.

i.e. - 10 items in column B are checked/Xd so row 36, column B should automatically populate as 28.5%.

any thoughts on how to get the formula for this into row 36, column B?

thx
Click to expand...
=COUNTIF(B1:B35,"X")/35 displayed as a percentage.

 
Chemical X said:
here is a word question;

wife has column A which contains 35 rows of action items.  column B is to be blank or have a check mark/X in it based on whether or not the item in column A has been completed.

row 36 column B she would like to have automatically calculate the % of items checked/Xd of the 35 items.

i.e. - 10 items in column B are checked/Xd so row 36, column B should automatically populate as 28.5%.

any thoughts on how to get the formula for this into row 36, column B?

thx
Click to expand...
i'm confused what you're asking here.  afaik, there's no way to implement formulas into word.  

 
What is the best way to provide an example .xls of what I am trying to do? Still trying to solve this, but so far unable, and would like to share a sample .xls with this group (or any specific user).

In short - I need to create a 'grand total' in a spreadsheet, and can use some assistance. There are some rules (counting with a specific month by types of incidents'), etc. 

Thank you

 
cubd8 said:
What is the best way to provide an example .xls of what I am trying to do? Still trying to solve this, but so far unable, and would like to share a sample .xls with this group (or any specific user).

In short - I need to create a 'grand total' in a spreadsheet, and can use some assistance. There are some rules (counting with a specific month by types of incidents'), etc. 

Thank you
Click to expand...
Google Sheets, although the formulas won't always be compatible.  But it should be the easiest way to put the data into the format that you have so we can see it and then copy/paste to Excel if we need to play with the data before answering.

 
Ned said:
Google Sheets, although the formulas won't always be compatible.  But it should be the easiest way to put the data into the format that you have so we can see it and then copy/paste to Excel if we need to play with the data before answering.
Click to expand...
Thank you. I haven't used google sheets before, but I can give this a shot this evening (firewall on my work computer).

Through google sheets, do I post my sample .xls and then provide a link on this site?

 
cubd8 said:
Thank you. I haven't used google sheets before, but I can give this a shot this evening (firewall on my work computer).

Through google sheets, do I post my sample .xls and then provide a link on this site?
Click to expand...
Yup - just click the share button when you're done with the sheet and it'll give you a link.

 
See the attached document

https://docs.google.com/spreadsheets/d/12VX9Ko9IZUIAw6w4ObpHaDJ7TY9AcrCOVoEjKJ2HCwM/edit?usp=sharing

Goal: I need to auto-populate (through a formula) B3 through I3 with the data from the 'Log' Tab.

In the tab ‘BBB Incident Detail’:
For each month (January through August), calculate the grand total adding the ‘Total Weight’ in the ‘Log’ tab to the logic below:
- If the same customer (Column A) has greater than/equal to two Incidents (Column D = “Incident”) in a 3 month period (current month and previous 2 months), then ADD 1 to the grand total.
- If the same customer (Column A) has greater than/equal to two High Incidents (Column D = “High Incident”) in a 3 month period (current month and previous 2 months), then ADD 2 to the grand total.
- If the same customer (Column A) has greater than/equal to two for a combination of ‘Incident’ or ‘High Incident’ (Column D), add 1.5 to the grand total.

In the 'Log' tab:

  • Column Q is correctly calculating 3.25 points based on the aggregate weights for 3 orders in January (1.5 + 1.5 + 0.25).
  • If there are GE 2 'Incident' (Column O) for a month (in this example, January), i want to add '1' to the grand total of points for ALL orders in January. Because this month only has 1 'incident' in January, no additional points should be added to the grand total of 3.25 for January.
  • However, there are 2 'High Incident' in January. In this scenario, if there are GE 'High Incident' (Column P), I should add '2' to the grand total of 3.25 in January, meaning the total for January is now 5.25.
  • Column R is actually Checking Column O and Column P for the occurrences mentioned above, but it doesn't provide a grand total, instead a column by column view that ends up double aggregating.
  • Ultimately, i need my 'BBB Incident Tab' to provide a grand total for each month.
How do I go about creating a 'grand total' for each month aggregating the total weights from Column N, but also accounting for the special rules above for 'Incident' and 'High Incident'. 

thank you!

 
cubd8 said:
See the attached document

https://docs.google.com/spreadsheets/d/12VX9Ko9IZUIAw6w4ObpHaDJ7TY9AcrCOVoEjKJ2HCwM/edit?usp=sharing

Goal: I need to auto-populate (through a formula) B3 through I3 with the data from the 'Log' Tab.

In the tab ‘BBB Incident Detail’:
For each month (January through August), calculate the grand total adding the ‘Total Weight’ in the ‘Log’ tab to the logic below:
- If the same customer (Column A) has greater than/equal to two Incidents (Column D = “Incident”) in a 3 month period (current month and previous 2 months), then ADD 1 to the grand total.
- If the same customer (Column A) has greater than/equal to two High Incidents (Column D = “High Incident”) in a 3 month period (current month and previous 2 months), then ADD 2 to the grand total.
- If the same customer (Column A) has greater than/equal to two for a combination of ‘Incident’ or ‘High Incident’ (Column D), add 1.5 to the grand total.

In the 'Log' tab:

  • Column Q is correctly calculating 3.25 points based on the aggregate weights for 3 orders in January (1.5 + 1.5 + 0.25).
  • If there are GE 2 'Incident' (Column O) for a month (in this example, January), i want to add '1' to the grand total of points for ALL orders in January. Because this month only has 1 'incident' in January, no additional points should be added to the grand total of 3.25 for January.
  • However, there are 2 'High Incident' in January. In this scenario, if there are GE 'High Incident' (Column P), I should add '2' to the grand total of 3.25 in January, meaning the total for January is now 5.25.
  • Column R is actually Checking Column O and Column P for the occurrences mentioned above, but it doesn't provide a grand total, instead a column by column view that ends up double aggregating.
  • Ultimately, i need my 'BBB Incident Tab' to provide a grand total for each month.
How do I go about creating a 'grand total' for each month aggregating the total weights from Column N, but also accounting for the special rules above for 'Incident' and 'High Incident'. 

thank you!
Click to expand...
Can you change the Google Sheet to allow folks with the link to edit?  It's set to View Only - This is a little involved and would be easier to add an example to your doc.

 
Ned said:
Can you change the Google Sheet to allow folks with the link to edit?  It's set to View Only - This is a little involved and would be easier to add an example to your doc.
Click to expand...
Thanks for the feedback, I just updated to allow for editing. Let me know if that resolves on your end.

 
cubd8 said:
Thanks for the feedback, I just updated to allow for editing. Let me know if that resolves on your end.
Click to expand...
I thought I understood what you wanted, but then I saw column Q:R in the Log and its giving you the totals already.  So why not just index that data into the BBB Incident Detail?

I added it to show you what I mean.  I also updated Q:R to check F instead of A.... =IF(F2<>F1 instead of =IF(A2<>A1

 
How would I update this formula to output a zero ('0') instead of N/A? 

=IF(IFERROR(VLOOKUP(B516,ReClassify!AU:AV,2,0),3)="Incident",DH516*0.25,IF(IFERROR(VLOOKUP(B516,ReClassify!AU:AV,2,0),3)="High Incident",DH516,0))*IF(INDEX(ReClassify!AY:AY,MATCH(B516,ReClassify!AU:AU,FALSE),1)>=2,1.5,1)

 
Spiderman said:
How would I update this formula to output a zero ('0') instead of N/A? 

=IF(IFERROR(VLOOKUP(B516,ReClassify!AU:AV,2,0),3)="Incident",DH516*0.25,IF(IFERROR(VLOOKUP(B516,ReClassify!AU:AV,2,0),3)="High Incident",DH516,0))*IF(INDEX(ReClassify!AY:AY,MATCH(B516,ReClassify!AU:AU,FALSE),1)>=2,1.5,1)
Click to expand...
Wrap it with an IFERROR.

=IFERROR( IF(IFERROR(VLOOKUP(B516,ReClassify!AU:AV,2,0),3)="Incident",DH516*0.25,IF(IFERROR(VLOOKUP(B516,ReClassify!AU:AV,2,0),3)="High Incident",DH516,0))*IF(INDEX(ReClassify!AY:AY,MATCH(B516,ReClassify!AU:AU,FALSE),1)>=2,1.5,1) , 0)

Or make it '0' instead of 0 if desired.

 
Question -

Here is my example:

Raw Data in Column BM: 1/25/2017
Formula in Column BN: =MONTH(BM2) 
Column BN displays a '1', in this example.

How would I update Column BN to display the month differently (Jan instead '1', Feb instead of '2', etc.)

I tried using a custom format (mmm), but I have been unable to get the formula to drag down (it keeps populating all values with 'Jan' instead of the actual expected month.

 
Spiderman said:
Question -

Here is my example:

Raw Data in Column BM: 1/25/2017
Formula in Column BN: =MONTH(BM2) 
Column BN displays a '1', in this example.

How would I update Column BN to display the month differently (Jan instead '1', Feb instead of '2', etc.)

I tried using a custom format (mmm), but I have been unable to get the formula to drag down (it keeps populating all values with 'Jan' instead of the actual expected month.
Click to expand...
Instead of MONTH, use TEXT.

=TEXT(BM2,"mmmm")

Edit: "mmm" if you want the 3 letter month instead.

 
Last edited by a moderator:
Walking Boot said:
An error I don't understand:

In Column A, I have a start date for a new hire for a project. Let's say it's Monday 5/1/17

In Column B, I have the expected duration for the project, given in weeks, where 0.2 weeks = 1 day, 0.8 weeks = 4 days, 1.0 weeks = 5 days, 1.6 weeks = 8 days, etc.

In Column C, I want to put the expected FINISH date for the new hire. So, if A1= Monday 5/1/17, B1= 1.0 weeks, C1 should be Friday 5/5/17.

The formula in C1 is =WORKDAY(A1, ((B1*5)-1), 0). WORKDAY takes 3 arguments. The first is the start date. The second is how many days after the first day to count. The third is how many holidays that aren't working days to skip. For purposes of this project, there are no holidays, we just want to count Monday-to-Friday days.

I'm using ((B1*5)-1) to convert the number of weeks into actual days. So if it's 0.8 weeks, it multiplies by 5 (4), subtracts 1, and returns 3. (This is because if the person is hired to work one day only, 0.2 weeks, we want the start and wrap date to be the same). So 5/1, 3 work days later is 5/4. The person has worked 0.8 weeks by working Monday-Thursday. This is correct.

This formula works EXCEPT when the value in B is between 1 and 2.4. Why? 

This is what I'm seeing:

A             B         C

Start    Weeks  Finish

5/1Mon   0.2    5/1Mon

5/1Mon   0.4    5/2Tue

5/1Mon   0.6    5/3Wed

5/1Mon   0.8    5/4Thu

5/1Mon   1.0    5/5Fri

5/1Mon   1.2   5/8Mon

5/1Mon   1.4   5/9Tue

5/1Mon   1.6   5/9Tue          

5/1Mon   1.8   5/11Thu

5/1Mon   2.0   5/11Thu

5/1Mon   2.2  5/12 Fri

5/1Mon   2.4  5/16Tue

5/1Mon   2.6  5/17Wed

5/1Mon   2.8  5/18Thu

5/1Mon   3.0  5/19Fri

5/1Mon   3.2  5/22Mon

5/1Mon   3.4  5/23Tue

5/1Mon   3.6  5/24Wed

5/1Mon   3.8  5/25Thu

5/1Mon   4.0  5/26Fri

The bolded dates are incorrect, they're a day short. I should be seeing a Monday-Friday pattern down the column.

I've run it out for 12.0 weeks, and this error pops up a couple more times, but this is where it's the most frequent.    

What am I not thinking about correctly?
Click to expand...
Is your column B raw numbers or is it another calc?  I just ran out your example and the bolded dates calculated right for me.  I'm wondering if you have some rounding going on in Col B that's causing the issue.

 
I can see the same issue.  It is fixed if you put this in column B:

=ROUND(B1+0.2,1)

then copy it down.

 
Say I have a ton (a ton!) of spreadsheets with the same format in multiple sheets, say sheets 1-4. I want to create a new sheet 5 that takes sheets 1-4 and straight adds cells. Sheet5A1 = Sheet1A1 + Sheet2A1... etc.

Is there an easier way that formatting the first sheet 5 and copy/pasting formulas to the rest of the workbooks?

Edit- It may be helpful to mention that the workbooks don't have names that are in order or anything (Workbook1, Workbook2, etc). They have varied names.

 
Last edited by a moderator:
Would think you would need to do a VBA macro to do them in batch. Not my strength unfortunately, have only fiddled with it mostly.

 
Worm said:
Say I have a ton (a ton!) of spreadsheets with the same format in multiple sheets, say sheets 1-4. I want to create a new sheet 5 that takes sheets 1-4 and straight adds cells. Sheet5A1 = Sheet1A1 + Sheet2A1... etc.

Is there an easier way that formatting the first sheet 5 and copy/pasting formulas to the rest of the workbooks?

Edit- It may be helpful to mention that the workbooks don't have names that are in order or anything (Workbook1, Workbook2, etc). They have varied names.
Click to expand...
According to this page, all you have to do is create a sum formula in Sheet 5, cell A1:

=sum(Sheet1:Sheet4!,A1)

Then copy to other cells as needed.

No idea how to do the other part.  I know Excel can look in Windows folders and grab sheets but would have to google how it's done.  Probably need a common naming convention to start. I'd ask an actuary.

 
Worm said:
Say I have a ton (a ton!) of spreadsheets with the same format in multiple sheets, say sheets 1-4. I want to create a new sheet 5 that takes sheets 1-4 and straight adds cells. Sheet5A1 = Sheet1A1 + Sheet2A1... etc.

Is there an easier way that formatting the first sheet 5 and copy/pasting formulas to the rest of the workbooks?

Edit- It may be helpful to mention that the workbooks don't have names that are in order or anything (Workbook1, Workbook2, etc). They have varied names.
Click to expand...
Check out Power Query.

 
Psychopav said:
According to this page, all you have to do is create a sum formula in Sheet 5, cell A1:

=sum(Sheet1:Sheet4!,A1)

Then copy to other cells as needed.

No idea how to do the other part.  I know Excel can look in Windows folders and grab sheets but would have to google how it's done.  Probably need a common naming convention to start. I'd ask an actuary.
Click to expand...
Yeah, I knew the sheet to sheet part. It is doing it in a way that is more automated for a bunch of different workbooks. Thanks GB.

 
Not sure if I can explain my question correctly, but let me try:

I am doing a multi-tiered financial waterfall based on XIRR. The first hurdle is 10%. I am trying to create a formula that prevents me from having to hardcode the distribution amount or do random inputs/math outside thr worksheet. 

I am trying to just keep it simple — more to it than all of this but I would like something in plain language like:

if IRR cell is less than 10% then increase distribution cell amount by $1 until IRR cell is 10%  — might be a circular function though  

We can probably do a goal seek function for a cell outside the equation and enter the amount into the equations,as that would at least eliminate the math, but I want to eliminate hardcoding if possible  

 
Last edited by a moderator:
Create a new date field:

If Column A is populated, output that date in Column C
If Column A is blank but Column B is populated, output that date in Column C.
If both Column A and Column B are both populated, output the most recent date in Column C.

Example 1:
Column A: 6/2/2017
Column B: 5/30/2017
Column C expected value: 6/2/2017

Example 2:
Column A: 
Column B: 5/30/2017
Column C expected value: 5/30/2017

 
Question for the group:

Row of data

C2 Value: 279

C3 Value:328

C4 Value:345

C5 Value:292

**

I need to create a new formula in C6 that interrogates the previous row of data (essentially a series of IF statements).

If the value of C5 is GT 280 and LT 300, output a value of 10 in C6.

If the value of C5 is LT 280, output a value of -10 in C6.

If the value of C5 is GE 300 and LE 315, output a value of 15 in C6.

If the value of C5 is GT 315 and LE 330, output a value of 20 in C6.

If the value of C5 is GE 330, output a value of 25 in C6.

 
cubd8 said:
Question for the group:

Row of data

C2 Value: 279

C3 Value:328

C4 Value:345

C5 Value:292

**

I need to create a new formula in C6 that interrogates the previous row of data (essentially a series of IF statements).

If the value of C5 is GT 280 and LT 300, output a value of 10 in C6.

If the value of C5 is LT 280, output a value of -10 in C6.

If the value of C5 is GE 300 and LE 315, output a value of 15 in C6.

If the value of C5 is GT 315 and LE 330, output a value of 20 in C6.

If the value of C5 is GE 330, output a value of 25 in C6.
Click to expand...
You are better off using a vlookup function instead of a bunch of if/then statements

 
cubd8 said:
Thank you! How would I go about doing that?
Click to expand...
Create a lookup table some where.  I picked L2 through M6, but it is 2 columns of info.  In the L column are the breaks you want.  In the M column are the values you want to return.  The L entries must be in ascending order, and the "equal to" part of what you are looking for.  You had a mix of GT and GE, but this is what I came up with using what you wrote:

0   -10

280   10

300   15

316   20

331   25

Then, put this formula into C6:

=VLOOKUP(C5,$L$2:$M$6,2,TRUE)

 
The way it works, is it looks at what is in C5 (292 in this case), then looks at what row in the "table" matches that.  Once it finds an entry that is higher, it reverts back one row.  Meaning, it would check 0 first.  Then would check 280.  Then would check 300.  Once it hits 300 (which is higher than 292), then it goes back one row (to 280) and returns the value you've assigned in the 2nd column ... 10.  If it hits a match (ie if you were looking up 316), then it returns the value in the 2nd column using the match it hit.

 
So I have 2 excel tabs I am trying to merge based on a common identifier.  The 1st tab has a lot of extra info and the 2nd tab is in a specific order of different groups.  So basically trying to import that additional info into the other tab and keep the order.  I could do it manually but it’s hundreds of entries

Not the specific info I am working with but here is an example

TAB 1

PART

NAME

COMMON IDENTIFIER

OTHER INFO

1

A

12345

Blue

2

B

67891

Green

3

C

54321

Yellow

4

D

19876

Red

TAB 2

COMMON IDENTIFIER

Days

12345

1

54321

2

19876

4

67891

3

So at the end I want to see a new sheet or whatever that would show

COMMON IDENTIFIER

Days

PART

NAME

OTHER INFO

12345

1

1

A

Blue

54321

2

3

C

Yellow

19876

4

4

D

Red

67891

3

2

B

Green

 
Dan Lambskin said:
So I have 2 excel tabs I am trying to merge based on a common identifier.  The 1st tab has a lot of extra info and the 2nd tab is in a specific order of different groups.  So basically trying to import that additional info into the other tab and keep the order.  I could do it manually but it’s hundreds of entries

Not the specific info I am working with but here is an example

TAB 1

PART

NAME

COMMON IDENTIFIER

OTHER INFO

1

A

12345

Blue

2

B

67891

Green

3

C

54321

Yellow

4

D

19876

Red

TAB 2

COMMON IDENTIFIER

Days

12345

1

54321

2

19876

4

67891

3

So at the end I want to see a new sheet or whatever that would show

COMMON IDENTIFIER

Days

PART

NAME

OTHER INFO

12345

1

1

A

Blue

54321

2

3

C

Yellow

19876

4

4

D

Red

67891

3

2

B

Green
Click to expand...
You can use an INDEX/MATCH for each column you want added.  Put it in the first row and then autofill down.

=INDEX(TAB 1 OTHER INFO COL,MATCH(TAB2 COMMON IDENTIFIER,TAB1 COMMON IDENTIFIER,0),0)

 
Ned said:
You can use an INDEX/MATCH for each column you want added.  Put it in the first row and then autofill down.

=INDEX(TAB 1 OTHER INFO COL,MATCH(TAB2 COMMON IDENTIFIER,TAB1 COMMON IDENTIFIER,0),0)
Click to expand...
Tried that and I can pull info for 1 cell in but when I fill down I get NA.

just to clarify the entries in tab 1 are in a completely different order than those in tab 2, but I can’t just sort because i need to keep tab 2 as is

 
Dan Lambskin said:
Tried that and I can pull info for 1 cell in but when I fill down I get NA.

just to clarify the entries in tab 1 are in a completely different order than those in tab 2, but I can’t just sort because i need to keep tab 2 as is
Click to expand...
Sort order shouldn't matter to INDEX/MATCH.

Any way you can put some samples in a google sheet so I can take a peek?

 
Easy Way: add a column to the left of the table in TAB1 and duplicate the Common Identifier. Use vlookup in TAB2 to add the columns of info you need, then delete the column you created in TAB2 after hardcoding the vlookup data.

Not as easy way: Do what is stated above with the INDEX and MATCH, but check and confirm you have absolute references ($) for the lookup target. That's probably why you're getting the N/A when you fill down. The target table is probably a different reference in each cell you see N/A.

 
Ned said:
Sort order shouldn't matter to INDEX/MATCH.

Any way you can put some samples in a google sheet so I can take a peek?
Click to expand...


Pigskin Fanatic said:
Easy Way: add a column to the left of the table in TAB1 and duplicate the Common Identifier. Use vlookup in TAB2 to add the columns of info you need, then delete the column you created in TAB2 after hardcoding the vlookup data.

Not as easy way: Do what is stated above with the INDEX and MATCH, but check and confirm you have absolute references ($) for the lookup target. That's probably why you're getting the N/A when you fill down. The target table is probably a different reference in each cell you see N/A.
Click to expand...
Thanks guys.  I’ll try this and report back

 
Dan Lambskin said:
Tried that and I can pull info for 1 cell in but when I fill down I get NA.

just to clarify the entries in tab 1 are in a completely different order than those in tab 2, but I can’t just sort because i need to keep tab 2 as is
Click to expand...
Did you pull the two tabs from different data sources?  Any chance there is extra white space and need to TRIM one or both of the common identifiers?   

(if you click in the white function bar after clicking in a cell, does the cursor blink directly after the data vs separate from the data?)

 
You must log in or register to reply here.

Similar threads

ragincajun
Excel AI!!!!
Replies
26
Views
986
culdeus
culdeus
L
Work Efficiency & Organization
Replies
15
Views
552
MAC_32
MAC_32
GordonGekko
What Are Harsh Truths About Real Life That You Would Prefer To Just Ignore ? (8/30)
Replies
5
Views
441
bigbottom
bigbottom

Users who are viewing this thread

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