***Official*** Excel Help Corner (4 Viewers)

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.

Click to expand...

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.

Click to expand...

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?

Click to expand...

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. 

Click to expand...

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.

Click to expand...

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.

Click to expand...

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.

Click to expand...

• 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)
  

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.

Click to expand...

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)
  

Click to expand...

• 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.
  

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.
  

Click to expand...

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

Click to expand...

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

Click to expand...

• 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
  

• 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)
  

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.

Click to expand...

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.

Click to expand...

Chemical X said:

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

Click to expand...

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.

Click to expand...

Chemical X said:

Very helpful says the boss....thx!

Click to expand...

acarey50 said:

No problem - glad I was able to help

Click to expand...

Nick Vermeil said:

Here is a stupid problem. One of my employees has done something in her program that causes her view to shrink when she pushes ctrl. So she can’t use shortcuts without the spreadsheet shrinking. I was thinking maybe sticky keys but all other programs are fine. 
 

Any ideas?  I’m about to uninstall and reinstall. 

Click to expand...

Nathan R. Jessep said:

Just by pressing the CTRL key alone, it zooms out?   Because CTRL plus scrolling the mouse wheel will zoom in/out in most programs. 

Click to expand...

Nick Vermeil said:

Just pressing the CTRL key.  It's the weirdest thing.  

Click to expand...

Nathan R. Jessep said:

Hmmm, have you checked the shortcuts list to see if she somehow added a custom one with the CTRL key?

Only other thing I can think of is if she somehow created a macro that invokes the Zoom function when the CTRL key is pressed. 

Click to expand...

Nick Vermeil said:

Here is a stupid problem. One of my employees has done something in her program that causes her view to shrink when she pushes ctrl. So she can’t use shortcuts without the spreadsheet shrinking. I was thinking maybe sticky keys but all other programs are fine. 
 

Any ideas?  I’m about to uninstall and reinstall. 

Click to expand...

Chemical X said:

here is a strange question;

in excel, my cell contents don’t appear in the formula bar…..almost like they are invisible.

thoughts?

Click to expand...

Chemical X said:

here is a strange question;

in excel, my cell contents don’t appear in the formula bar…..almost like they are invisible.

thoughts?

Click to expand...

Ned said:

Did you try expanding the formula bar height to see if there's extra carriage returns in the cells themselves?

Click to expand...

SaintsInDome2006 said:

Hi ExcelGuys - quick question for some work I have to do tonight…

Is there an easy way to run a tool or a formula to see differences in two lists? So say Project 1 was done 4 weeks ago and Project 3 was run two days ago and they use the same fields in columns A-D. Is there a way I can tell the new entries just by running a certain formula, tool, button or option? 

I’ve started this manually (I’ll grind through it) but there must be an easier way. THANKS as always.

Click to expand...

SaintsInDome2006 said:

Hi ExcelGuys - quick question for some work I have to do tonight…

Is there an easy way to run a tool or a formula to see differences in two lists? So say Project 1 was done 4 weeks ago and Project 3 was run two days ago and they use the same fields in columns A-D. Is there a way I can tell the new entries just by running a certain formula, tool, button or option? 

I’ve started this manually (I’ll grind through it) but there must be an easier way. THANKS as always.

Click to expand...

Chemical X said:

i have nothing to add, but i use mrexcel.com sometimes and those guys are money…..

Click to expand...

Dinsy Ejotuz said:

Hey SID!  Long time.

Can you unpack this a little bit?  Not sure I'm following you.  What are you doing manually?

Click to expand...

SaintsInDome2006 said:

Hola! Yes indeed, hope you & yours are well, I'm good. I wrote this while at the Saints game hoping there might be some advice when I landed back at my desk.

Ok: 4 columns of info, A-D. Pretty simple.

I've received two worksheets. One version of this worksheet started 3 weeks ago and it's cumulative. So say it analyzed numbered items out of a group of 1000. Say that first sheet analyzed 250 rows of data 3 weeks ago.

- But now, 3 weeks later, I've received an update, and it has say 480 total pieces reviewed. Some of the original 250 is in that 480, but it's not indicated which are new, and I'm wondering if XL can simply identify which ones (230) are new.

I can do this manually by (eta: sorting by col. A) highlighting the 480 from the 2nd set, drop in the rows from the 1st sheet, and any time there's a new entry it won't have a matching non-highlighted row. So I am grinding through this data using this manual self-coded system, deleting the matches and highlighting in a different color the new ones.

So, basically, is there a tool or button I can employ to do this automatically without doing this drill?

Sorry, don't mean to cause any homework for anyone. Winston played great tonight and feeling fine in the A/C. - Any advice is appreciated. - Thanks.

Click to expand...

MTskibum said:

Copy/paste the new data directly below the old data and then remove duplicates.

You can highlight the new data first if you want to keep track of what is new.

You could also concatenate the columns into column e in both data sets and then do a vlookup on 1 column e into the other column e.

Probably a few more ways to do it as well.

Click to expand...

MTskibum said:

Copy/paste the new data directly below the old data and then remove duplicates.

You can highlight the new data first if you want to keep track of what is new.

You could also concatenate the columns into column e in both data sets and then do a vlookup on 1 column e into the other column e.

Probably a few more ways to do it as well.

Click to expand...

Ned said:

 That's the most direct way to do it.

Info on removing duplicates

Click to expand...

SaintsInDome2006 said:

I did the first thing, I think the concatenation is something I need to revisit. I've done it before but it's been a while. I appreciate the reply. -SID

Click to expand...

bryhamm said:

concatenate is very easy.  some use the formula, but an easier way is to simply use the & sign.  Like this:

=A1&B1

Put that in C1 and you get the contents of A1 and B1 merged together.

Click to expand...

culdeus said:

I've got a problem that I've tried a few solutions for and haven't locked it down.

I have a string of numbers and letters.  I need the 8 characters immediately before a specific string of 3 letters.  This can come anywhere in a string that is about 100 characters long.

So like

1223898AAXETAAST2310341ABC132305uAASERWA1@#%^AAAt235ZOMG

I need the 8 characters immediately before "ABC" where the next string may not have the ABC in this exact spot.

Click to expand...

coopersdad said:

Ok, have an issue that I'm not sure how to solve.  I've got a table of cells with text.  In each cell an account is separated from the description by a dash.  I need to parse the account into one cell, and then parse the account to another.  Basically taking everything from the right of the dash into on cell and everything to the right into another.  And all the accounts / descriptions are of different length. 

Click to expand...

Ned said:

https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

Your delimiter would be the dash.

Click to expand...

dino259 said:

I am using the formula DATE(RIGHT(F2,2),LEFT(F2,2),MID(F2,3,2)) to convert 6 digits of 112621 to a date.  However it is giving me 11/26/1921 instead of the desired 11/26/2021.  Any thoughts?

Click to expand...