Jump to content
Fantasy Football - Footballguys Forums

***Official*** Excel Help Corner


Recommended Posts

19 minutes ago, HaFo SaFo said:

Strange problem, my zeros aren't zero sometimes... Ever seen this and anything I can do about it? Or has my spreadsheet stumbled into a floating point error and this will never be fixed and I should just hard-code the answer?

(And, yes, that's happened to me before, I have a formula that divides a whole number by 5, so my count goes up incrementally by 0.2, but every so often 6/5 is 1.2 and then 7/5 is also 1.2 because of floating point math and then 8/5 is 1.6 and I never get a 1.4 in the sequence... "0.2, 0.4, 0.6, 0.8, 1.0, 1.2, 1.2 ,1.6" and when we went to Microsoft support they said "Excel uses floating point math and it will never be correct, and we can't fix it without re-inventing both the computer, and mathematics as a whole, so you're out of luck. Stop using decimals, use whole numbers only, and your problems will go away.")

 

Anyway, I have a spreadsheet that tracks repayments. I have how much I lent out, and then every time a partial payment comes in, it shows me how much I have left. So, for example, if I lend out $100 and I get paid back $20, I put $100 in column A, $20 in column B, then Column C is A-B and shows I have $80 owed to me still.

Fine.

Now, sometimes, I get paid back a little extra. Like, I'll get a tip. So if I'm paid back $105 it'll say I'm owed $-5. For neatness, I'd rather just show $0 owed to me. So I put in a formula: MAX(A-B,0). So if I'm paid back $105 it'll instead show the MAX of the values of -5 or 0, so... 0. Right?

But in one row in particular, row 13 of 50, for just this one row, the math doesn't add up. I have been paid back all $100 so I expect the answer to be zero. But, instead, when it evaluates A-B it gets 0. OK. But when I did the MAX(A-B, 0), the answer it shows is: "1.42E-14".

 

1.42E-14... that's a floating point error and I'm SOL, right? No other way to get my negative numbers to evaluate to zero without using MAX or an IF formula, so I think I'm dead and I'm just going to have to hard-key in a 0. 

 

Stupid math.

Not sure what downstream implications there might exist. If the c column is just for the visuals, how about =MAX(A-B-0.0001,0) then display to the whole dollar or whole cent? Would that take care of the problem?

Link to post
Share on other sites
  • Replies 921
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

Also sometimes where that doesn't turn out well, I've pasted into Notepad then copy and pasted that to Excel and it's done better.

I'm an Excel nerd to the hilt and enjoy helping others when it comes to Excel problems, so I figured I'd start a thread where y'all can ask questions on how to do certain things in Excel. I'm game fo

3 hours ago, HaFo SaFo said:

Strange problem, my zeros aren't zero sometimes... Ever seen this and anything I can do about it? Or has my spreadsheet stumbled into a floating point error and this will never be fixed and I should just hard-code the answer?

(And, yes, that's happened to me before, I have a formula that divides a whole number by 5, so my count goes up incrementally by 0.2, but every so often 6/5 is 1.2 and then 7/5 is also 1.2 because of floating point math and then 8/5 is 1.6 and I never get a 1.4 in the sequence... "0.2, 0.4, 0.6, 0.8, 1.0, 1.2, 1.2 ,1.6" and when we went to Microsoft support they said "Excel uses floating point math and it will never be correct, and we can't fix it without re-inventing both the computer, and mathematics as a whole, so you're out of luck. Stop using decimals, use whole numbers only, and your problems will go away.")

 

Anyway, I have a spreadsheet that tracks repayments. I have how much I lent out, and then every time a partial payment comes in, it shows me how much I have left. So, for example, if I lend out $100 and I get paid back $20, I put $100 in column A, $20 in column B, then Column C is A-B and shows I have $80 owed to me still.

Fine.

Now, sometimes, I get paid back a little extra. Like, I'll get a tip. So if I'm paid back $105 it'll say I'm owed $-5. For neatness, I'd rather just show $0 owed to me. So I put in a formula: MAX(A-B,0). So if I'm paid back $105 it'll instead show the MAX of the values of -5 or 0, so... 0. Right?

But in one row in particular, row 13 of 50, for just this one row, the math doesn't add up. I have been paid back all $100 so I expect the answer to be zero. But, instead, when it evaluates A-B it gets 0. OK. But when I did the MAX(A-B, 0), the answer it shows is: "1.42E-14".

 

1.42E-14... that's a floating point error and I'm SOL, right? No other way to get my negative numbers to evaluate to zero without using MAX or an IF formula, so I think I'm dead and I'm just going to have to hard-key in a 0. 

 

Stupid math.

Have you tried clearing the contents of the cell (not just deleting, but selecting clear contents), and reentering the formula?

Link to post
Share on other sites

Existing Formula:

The logic checks the Column K (rows through 2-14) for "Rej"

=SUM(IF(FREQUENCY(IF($A$2:$A$14=A2,IF($D$2:$D$14="Final",IF($I$2:$I$14="C D w/ I",IF(ISERROR(SEARCH("Rej",$K$2:$K$14)),$E$2:$E$14)))),--$E$2:$E$14),$E$2:$E$14))

How would I remove the logic to check for "Rej" without impacting the rest of this formula?

Link to post
Share on other sites
  • 3 weeks later...

Hi all much respect and appreciation for your past help. I have a new question:

Is there a way to filter for all rows associated with a given entry?

For instance if A is invoice no, B is customer and C is product (and then B & C may vary as the invoice no. In A stays the same for multiple rows)...

.... is there a way that I can pull in all entries for that given invoice no. in A?

Link to post
Share on other sites

hey all, wife is back with a pair of vlookup type questions.  These are way over my head, but hopefully i can explain;

she has a source sheet and a sheet that she set up to pull info from the source.  in column A of the source, which is her driver here, there are properties with numeric 5 digit values.  i.e. - 12345.  (columns b thru whatever have info too - but column A of the source is pulling the info).  there are also properties with this type of value; 12345-01 or 12345-02.  the sheet she has set up only has 5 digit numbers.

1) on her sheet, only the properties that have dashes, i.e. 12345-01 are moving info to her sheet.  properties without a dash are not moving info........what would be the cure for this?

2) on her created sheet, lets say she has 12345.  on the source sheet, there is 12345-01, 12345-02, 12345-03, etc.  she'd like the info from all the dash whatevers to populate into her sheet.  i.e. - 12345-01, 12345-02, 12345-03 each have a square footage associated with them.  she'd like these values to populate into her 12345 overall square footage as a combined total.  

so, her sheet has no dashes, but from the source, she'd like anything separated by a dash, 12345-01, 12345-02, to consolidate into her main 12345 row.

no idea how these things work, but any theories might push her in the right direction and would be greatly appreciated. 

Link to post
Share on other sites
On 3/13/2021 at 10:58 AM, SaintsInDome2006 said:

Hi all much respect and appreciation for your past help. I have a new question:

Is there a way to filter for all rows associated with a given entry?

For instance if A is invoice no, B is customer and C is product (and then B & C may vary as the invoice no. In A stays the same for multiple rows)...

.... is there a way that I can pull in all entries for that given invoice no. in A?

Unless I'm misunderstanding the question, you should be able to simply turn on filtering and then could easily filter row A for a particular invoice number, and it will return all rows with that invoice number.

Just select any cell in the top row (presumably your headers), then on the "Data" tab, there is a section called "Sort & Filter". Click on Filter and you will now see the down arrows in each cell of the top row. If you click on those, you will get all sorts of filtering options.

  • Like 1
Link to post
Share on other sites
On 3/13/2021 at 3:15 PM, Chemical X said:

hey all, wife is back with a pair of vlookup type questions.  These are way over my head, but hopefully i can explain;

she has a source sheet and a sheet that she set up to pull info from the source.  in column A of the source, which is her driver here, there are properties with numeric 5 digit values.  i.e. - 12345.  (columns b thru whatever have info too - but column A of the source is pulling the info).  there are also properties with this type of value; 12345-01 or 12345-02.  the sheet she has set up only has 5 digit numbers.

1) on her sheet, only the properties that have dashes, i.e. 12345-01 are moving info to her sheet.  properties without a dash are not moving info........what would be the cure for this?

2) on her created sheet, lets say she has 12345.  on the source sheet, there is 12345-01, 12345-02, 12345-03, etc.  she'd like the info from all the dash whatevers to populate into her sheet.  i.e. - 12345-01, 12345-02, 12345-03 each have a square footage associated with them.  she'd like these values to populate into her 12345 overall square footage as a combined total.  

so, her sheet has no dashes, but from the source, she'd like anything separated by a dash, 12345-01, 12345-02, to consolidate into her main 12345 row.

no idea how these things work, but any theories might push her in the right direction and would be greatly appreciated. 

I'm not following all of the sheets, etc., but can she add a column to the source sheet that has the dashes on it.

Ideally, she'd want to be able to add a column that essentially has the master 5 digit value only in it (ie, if column A is 12345-01, the added column would be 12345. Same for if column A is 12345-02, etc.) That can be accomplished using a LEFT() formula to bring in those first 5 digits. Then, on her sheet where she wants to sum everything with the same leading 5 characters, she could do a SUMIF formula to add all the square footages for everything that matches here reference in that new column (so everything with 12345 in the new column would be summed up, etc.)

Regarding your question #1, there are a ton of possibilities - what is she using to pull in the info? If it's a VLookup or other similar lookup formula, then the lookups need to be an exact match (yes, there are option to do a closest to match, but in this case an exact match seems needed), so usually the first thing I'd look for is any differences in the lookup columns - trailing or leading spaces, something like that which would make the values not a match

  • Thanks 1
Link to post
Share on other sites
5 hours ago, acarey50 said:

Unless I'm misunderstanding the question, you should be able to simply turn on filtering and then could easily filter row A for a particular invoice number, and it will return all rows with that invoice number.

Just select any cell in the top row (presumably your headers), then on the "Data" tab, there is a section called "Sort & Filter". Click on Filter and you will now see the down arrows in each cell of the top row. If you click on those, you will get all sorts of filtering options.

Thanks. Well the problem is say imagine 250,000 rows of invoices.

So say a company sells multiple product lines - widget1, widget2, widget3, widget4, widget5, etc.

So a given invoice may be:

Invoice No.     Product    Date

5900623        Widget3     5/9/2008

5900623        Widget2     5/9/2008

5900623        Widget8     5/9/2008

(etc.)

And then imagine thousands upon thousands of invoices, so it's basically impossible to just "know" the invoices you want. You just want to pull all products involved with any given invoice that has at least one sale of Widget3.

Follow?

TIA, many times over for any who have any idea how to do this.

Link to post
Share on other sites
19 minutes ago, SaintsInDome2006 said:

Thanks. Well the problem is say imagine 250,000 rows of invoices.

So say a company sells multiple product lines - widget1, widget2, widget3, widget4, widget5, etc.

So a given invoice may be:

Invoice No.     Product    Date

5900623        Widget3     5/9/2008

5900623        Widget2     5/9/2008

5900623        Widget8     5/9/2008

(etc.)

And then imagine thousands upon thousands of invoices, so it's basically impossible to just "know" the invoices you want. You just want to pull all products involved with any given invoice that has at least one sale of Widget3.

Follow?

TIA, many times over for any who have any idea how to do this.

Your original question was about "a given entry", implying you would know a specific invoice #.  I'm not sure how you could "filter for all rows associated with a given entry" without knowing a data value associated with that entry.

  • Thanks 1
Link to post
Share on other sites
14 minutes ago, SaintsInDome2006 said:

Thanks. Well the problem is say imagine 250,000 rows of invoices.

So say a company sells multiple product lines - widget1, widget2, widget3, widget4, widget5, etc.

So a given invoice may be:

Invoice No.     Product    Date

5900623        Widget3     5/9/2008

5900623        Widget2     5/9/2008

5900623        Widget8     5/9/2008

(etc.)

And then imagine thousands upon thousands of invoices, so it's basically impossible to just "know" the invoices you want. You just want to pull all products involved with any given invoice that has at least one sale of Widget3.

Follow?

TIA, many times over for any who have any idea how to do this.

That's a bit more complicated that how I interpreted your initial questions of "is there a way that I can pull in all entries for that given invoice no. in A?" which I interpreted as filtering for every entry with that invoice number.

I'm not sure I completely follow though what it is you are trying to be able to do.

  • Are you saying you want to be able to find every other product ever sold on any invoice that includes widget 3?
  • Are you just looking for every invoice where widget 3 was sold? Easy enough, just filter on widget, though I am guessing this is not what you are asking.
  • Something else (I swear I am not being intentionally obtuse, I think I am just truly not understanding what it is you are trying to accomplish)
Link to post
Share on other sites
1 hour ago, heckmanm said:

Your original question was about "a given entry", implying you would know a specific invoice #.  I'm not sure how you could "filter for all rows associated with a given entry" without knowing a data value associated with that entry.

Thanks, yes I agree - sorry - I originally fired this off in the hopes of pushing forward. Sorry for the confusion or inconvenience.

Link to post
Share on other sites
1 hour ago, acarey50 said:

That's a bit more complicated that how I interpreted your initial questions of "is there a way that I can pull in all entries for that given invoice no. in A?" which I interpreted as filtering for every entry with that invoice number.

I'm not sure I completely follow though what it is you are trying to be able to do.

  • Are you saying you want to be able to find every other product ever sold on any invoice that includes widget 3?
  • Are you just looking for every invoice where widget 3 was sold? Easy enough, just filter on widget, though I am guessing this is not what you are asking.
  • Something else (I swear I am not being intentionally obtuse, I think I am just truly not understanding what it is you are trying to accomplish)

Eh, alright, I do have 250,000 rows of data. basically I am trying to avoid doing what I need to do linearly or manually. That is I do not want to squint at each row of data one by one to carefully examine it, I'd like to eliminates the invoices (sales) which I do not need to see, which would save me a lot of trouble, mistakes and eye strain.

To answer your question - of the three:

  • Yes.
  • No.
  • Ha, thanks, man, it's me, I'm the difficult one, I've failed at explaining (and I really really appreciate you help). - Ok I will try to summary this better - without revealing what I do, which is complicated - say it's permissible to sell Widget3 with Widget1 but not Widget2. - I'm trying to go through all the invoices where Widget3 has been sold, so I can look at all the products sold with Widget3, so I can see which sales (ie invoices) were appropriate/permissible, and which were not.
Edited by SaintsInDome2006
Link to post
Share on other sites
11 minutes ago, SaintsInDome2006 said:

Eh, alright, I do have 250,000 rows of data. basically I am trying to avoid doing what I need to do lienarly or manually.

To answer your question - of the three:

  • Yes.
  • No.
  • Ha, thanks, man, it's me, I'm the difficult one, I've failed at explaining (and I really really appreciate you help). - Ok I will try to summary this better - without revealing what I do, which is complicated - say it's permissible to sell Widget3 with Widget1 but not Widget2. - I'm trying to go through all the invoices where Widget3 has been sold, so I can look at all the products sold with Widget3, so I can see which sales (ie invoices) were appropriate/permissible, and which were not.

Ok - that is quite a bit more complicated.

Best I can do is provide this link that is for result from a request about using the same field as both row and column in a Pivot Table (you can't from what I can find) to count the distinct combinations, essentially what you are trying to do with the product combinations within an invoice.

I think the PowerPivot solution has promise to at least help you identify how often any impermissible combinations were sold together, but it may not get you to the level of being able to identify the exact invoices/customers.

 

One other option, and this really depends on how many impermissible combinations there are, would be to put the data into a pivottable, Invoice number as the row (could also add customer name or any other identifier you need, just set it as a tabular layout with repeating item labels), product line as the column, and, if you have a quantity field, use that for the value, otherwise any field that isn't blank as the data as you at least want a 1 to show up for when there is a product sold on a particular invoice.

Then, for example, you could filter all invoices with say Widget 1, and if you need to see if Widget 4 was ever sold with it, you then filter for widget 4, and you now have your list of every invoice where that product combination was sold.

If there is a large number of impermissible combinations, then perhaps we could take the pivottable results and build another check to run through the various combinations to flag any invoice with an impermissible combination.

  • Thanks 1
Link to post
Share on other sites
On 3/15/2021 at 11:24 AM, acarey50 said:

I'm not following all of the sheets, etc., but can she add a column to the source sheet that has the dashes on it.

Ideally, she'd want to be able to add a column that essentially has the master 5 digit value only in it (ie, if column A is 12345-01, the added column would be 12345. Same for if column A is 12345-02, etc.) That can be accomplished using a LEFT() formula to bring in those first 5 digits. Then, on her sheet where she wants to sum everything with the same leading 5 characters, she could do a SUMIF formula to add all the square footages for everything that matches here reference in that new column (so everything with 12345 in the new column would be summed up, etc.)

Regarding your question #1, there are a ton of possibilities - what is she using to pull in the info? If it's a VLookup or other similar lookup formula, then the lookups need to be an exact match (yes, there are option to do a closest to match, but in this case an exact match seems needed), so usually the first thing I'd look for is any differences in the lookup columns - trailing or leading spaces, something like that which would make the values not a match

This was helpful and pushed her in the right direction, but now she has the following issue, which is somewhat related to question 2;

so, on her source sheet, let's say she has a row of values she wants to add (i.e. row 2, columns a, b, c, d).  she wants to add these 4 figures and have them move to her created sheet into 1 space (i.e. a1).  any ideas on how to accomplish this?

for reference, the source sheet has square footage totals, let's say a2, b2, c2, d2 each being 25,000.  on her created sheet for the matching property,  she has 1 space to total square footage.  so, she'd like that space to be 100,000.

Thx

 

 

Link to post
Share on other sites
58 minutes ago, Chemical X said:

This was helpful and pushed her in the right direction, but now she has the following issue, which is somewhat related to question 2;

so, on her source sheet, let's say she has a row of values she wants to add (i.e. row 2, columns a, b, c, d).  she wants to add these 4 figures and have them move to her created sheet into 1 space (i.e. a1).  any ideas on how to accomplish this?

for reference, the source sheet has square footage totals, let's say a2, b2, c2, d2 each being 25,000.  on her created sheet for the matching property,  she has 1 space to total square footage.  so, she'd like that space to be 100,000.

Thx

 

 

Will the second sheet have the property number as a unique identifier? And are the number of columns to be summed the same for each property, or at the very least is there a max number of columns that may need to be summed up?

If there is a unique identifier, and a cap on the number of columns with square footages listed (though technically you could work around this), then I believe your two best options would be to either use a SUMIF formula on the created sheet, or, if you can add a column with total square footage to the source, you could do a lookup on the created sheet to pull over the total square footage.

This may be a simplistic version of what she actually needs to do, but let's say on your source sheet the columns are:

  • Column A - Property (unique identifier)
  • Column B - SQ Footage 1
  • Column C - SQ Footage 2
  • Column D - SQ Footage 3
  • Column E - SQ Footage 4

Then, on her created sheet, you would have:

  • Column A - Property (the unique identifier)
  • Column B - Total Square Footage (SUMIF formula that will look for the matching Property number and return the sum of columns A thru D)

The nice thing about using this is if your source sheet has multiple line items for each property (not knowing the actual source data, not sure if this is possible), then it will sum up the values from all the columns for all of those rows that match the Property value.

I think this is likely the best option from what I understand of the project, but if it won't work for some reason we can look at the option of adding a total square footage column to the source and pulling from there. It is essentially the same process, especially if it is possible for a property to have multiple rows on the source.

 

Link to post
Share on other sites
2 minutes ago, acarey50 said:

Will the second sheet have the property number as a unique identifier? And are the number of columns to be summed the same for each property, or at the very least is there a max number of columns that may need to be summed up?

If there is a unique identifier, and a cap on the number of columns with square footages listed (though technically you could work around this), then I believe your two best options would be to either use a SUMIF formula on the created sheet, or, if you can add a column with total square footage to the source, you could do a lookup on the created sheet to pull over the total square footage.

This may be a simplistic version of what she actually needs to do, but let's say on your source sheet the columns are:

  • Column A - Property (unique identifier)
  • Column B - SQ Footage 1
  • Column C - SQ Footage 2
  • Column D - SQ Footage 3
  • Column E - SQ Footage 4

Then, on her created sheet, you would have:

  • Column A - Property (the unique identifier)
  • Column B - Total Square Footage (SUMIF formula that will look for the matching Property number and return the sum of columns A thru D)

The nice thing about using this is if your source sheet has multiple line items for each property (not knowing the actual source data, not sure if this is possible), then it will sum up the values from all the columns for all of those rows that match the Property value.

I think this is likely the best option from what I understand of the project, but if it won't work for some reason we can look at the option of adding a total square footage column to the source and pulling from there. It is essentially the same process, especially if it is possible for a property to have multiple rows on the source.

 

i think you re accurate....gonna slide this by the boss and see what happens.  many thx for all the help.

Link to post
Share on other sites
4 minutes ago, acarey50 said:

Will the second sheet have the property number as a unique identifier? And are the number of columns to be summed the same for each property, or at the very least is there a max number of columns that may need to be summed up?

If there is a unique identifier, and a cap on the number of columns with square footages listed (though technically you could work around this), then I believe your two best options would be to either use a SUMIF formula on the created sheet, or, if you can add a column with total square footage to the source, you could do a lookup on the created sheet to pull over the total square footage.

This may be a simplistic version of what she actually needs to do, but let's say on your source sheet the columns are:

  • Column A - Property (unique identifier)
  • Column B - SQ Footage 1
  • Column C - SQ Footage 2
  • Column D - SQ Footage 3
  • Column E - SQ Footage 4

Then, on her created sheet, you would have:

  • Column A - Property (the unique identifier)
  • Column B - Total Square Footage (SUMIF formula that will look for the matching Property number and return the sum of columns A thru D)

The nice thing about using this is if your source sheet has multiple line items for each property (not knowing the actual source data, not sure if this is possible), then it will sum up the values from all the columns for all of those rows that match the Property value.

I think this is likely the best option from what I understand of the project, but if it won't work for some reason we can look at the option of adding a total square footage column to the source and pulling from there. It is essentially the same process, especially if it is possible for a property to have multiple rows on the source.

 

do you know a formula for this....?

Link to post
Share on other sites
50 minutes ago, Chemical X said:

do you know a formula for this....?

Assuming you mean the SUMIF formula, here is the microsoft page on the formula

For your case, in the cell where you want to have the total square footage you essentially would put:

=SUMIFS(SourceSheet!$B:$E,SourceSheet!$A:$A,A2)

The actual reference to SourceSheet will be based on the name of the source file (if the source sheet and her created sheet are in separate files) and the actual sheet name in the file (assuming not the default Sheet1)

 

I used SUMIFS (it is essentially the same as SUMIF) as it allows the use of multiple criteria range should that be a necessity, either in the future or just in case the real sheet/needs is a bit more complex than the sample case.

 

Link to post
Share on other sites
17 hours ago, acarey50 said:

Assuming you mean the SUMIF formula, here is the microsoft page on the formula

For your case, in the cell where you want to have the total square footage you essentially would put:

=SUMIFS(SourceSheet!$B:$E,SourceSheet!$A:$A,A2)

The actual reference to SourceSheet will be based on the name of the source file (if the source sheet and her created sheet are in separate files) and the actual sheet name in the file (assuming not the default Sheet1)

 

I used SUMIFS (it is essentially the same as SUMIF) as it allows the use of multiple criteria range should that be a necessity, either in the future or just in case the real sheet/needs is a bit more complex than the sample case.

 

Very helpful says the boss....thx!

Link to post
Share on other sites
  • 2 weeks later...
On 3/17/2021 at 10:36 AM, acarey50 said:

No problem - glad I was able to help

new problem for you or the group;

wife has an excel sheet.  let’s say column a are city names and column b is square footage of space.  if she types the acronym bau in column c she’d like column d to have the value of column a copied over....this would be for the whole chart.  

so, if any row in column c gets bau, that row’s column b value will repeat in column d.

 

thx

 

 

Link to post
Share on other sites

Seems like a pretty simple IF statement if understanding this correctly.

IN column D, let's assume starting in row 2

=IF(C2="BAU",B2,"")

Copy that down column D

What that formula is doing is looking at column C, and if it has BAU in it, it will show the value from column B, if it doesn't, it will show as blank.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Recently Browsing   0 members

    No registered users viewing this page.


×
×
  • Create New...