Excel question

[zujca]

How do I make excel always put the sum of Bought for and Improvements to the Total spent and to make the Cr earned Profit - (minus) total spent?

Thank you!

 

[DQuaN]

Ideally we need to see the Columns but assuming Bought for is E, Improvements F, Total Spent G, Profit H and Cr earned I for example, you'd need something like =(E5+F5) for G5 and then =(H5-G5) for I5.

Assuming the numbers are on line 5.

 

[zujca]

Ideally we need to see the Columns but assuming Bought for is E, Improvements F, Total Spent G, Profit H and Cr earned I for example, you'd need something like =(E5+F5) for G5 and then =(H5-G5) for I5.

Assuming the numbers are on line 5.

I knew how to do that, but is there an option to make the whole G a sum of E and F, not for every line separately?

 

[Famine]

If the values are from cells E2 to F7, "=SUM(E2:F7)" in cell G2 and "=H2-G2" in cell I2.

 

[zujca]

If the values are from cells E2 to F7, "=SUM(E2:F7)" in cell G2 and "=H2-G2" in cell I2.

But I do need to do it for every line/car separately, right?
I was asking whether there is something like H(n)=F(n)+G(n), n being the line. It's because there's going to be a plenty of cars on the list so it would be quicker if every line of H column would be the sum of cells in F and G column in the same line.

 

[DQuaN]

After putting the sum in the last cell, click the little + in the corner of the cell and drag it down.

 

[zujca]

After putting the sum in the last cell, click the little + in the corner of the cell and drag it down.

Thank you! It works!

 

[BobK]

Not sure if I understand your question(s) here so I'll just say this: If you put "=sum(F2:G2)" in H2 then copy H2 to H3 then what gets copied into H3 is "=sum(F3:G3)". Better yet, if you copy H2 then select cells H3 through H10 then paste, each cell in the H column will reflect the the sum of the F and G cells in the same line; eg. H7 will have "=sum(F7:G7)" etc.

Edit: Or what DQuaN said. Treed.

 

[Matej]

I need help with summing in Excel. How do I sum up all cells that contain exactly the same value?

For instance, I have 300 cells in a column, some of them have value "1", some "4". How can I get total amount of cells that have value "4"?

 

[Famine]

I need help with summing in Excel. How do I sum up all cells that contain exactly the same value?

For instance, I have 300 cells in a column, some of them have value "1", some "4". How can I get total amount of cells that have value "4"?

=COUNTIF(cell:cell,"4")

 

[Matej]

=COUNTIF(cell:cell,"4")

It doesn't work, I get an error.

Edit: Ha, not anymore, problem solved. Thanks!

 

[DG_Silva]

For instance, I have 300 cells in a column, some of them have value "1", some "4". How can I get total amount of cells that have value "4"?

I've been working a lot in Excel recently, so if I can share my tuppence worth. If you wanted to change the number you are searching for, it's say the range of cells is B1:B300, we can make the instruction look into another cell for the value.

We can set C1 to be an input box for the number you are looking for, 1-4, and C2 to return the value. We set C2 to be =COUNTIF(B1:B300,C1). This way you won't need to change any formulae, just the value you are looking for in C1.

 

[Matej]

When you insert a chart in a document, normally you can go back and do some changes to the chart if needed. But what do to when a chart is inserted in a document as a picture? Is it possible to convert it back to editable chart?

 

[DG_Silva]

I'm glad I Googled this first before answering; when using something like Word, you can insert a chart from Excel or you can create a spreadsheet in the Word/PowerPoint file, edit the data behind it as necessary.

If you create a spreadsheet in Word, copy and paste the data directly from Excel, it will create a link from the original sheet and update as the original Excel file updates; this will make any chart you get from it update as well.

https://support.office.com/en-gb/ar...nto-Word-0b4d40a5-3544-4dcd-b28f-ba82a9b9f1e1

 

[Matej]

But what if you insert a chart in Word as a photo? I have received a document like that, and I had to use paint to manually change terms in it because I couldn't edit the chart via Excel.

 

[daan]

If the chart is in the document as an image then no, you can't edit it other than the way you did. It may be better to re-create the chart and insert it into word as a chart object for future use though.

 

[Matej]

Any good formula for multiplying numbers? None of the ones I tried don't work.

 

[MatskiMonk]

Any good formula for multiplying numbers? None of the ones I tried don't work.

=PRODUCT(range/values)

 

[TB]

Any good formula for multiplying numbers? None of the ones I tried don't work.

I'm assuming it's more complicated than =(Cell1*Cell2)?

 

[Matej]

=PRODUCT(range/values)

I'm assuming it's more complicated than =(Cell1*Cell2)?

I tried those before but they don't work, the cell doesn't update, it just registers the formula as a simple text entry. Summing formula works properly, so I don't know what could be the problem with multiplying.

 

[MatskiMonk]

I tried those before but they don't work, the cell doesn't update, it just registers the formula as a simple text entry. Summing formula works properly, so I don't know what could be the problem with multiplying.

There's a number of reasons why that could happen, but start by checking the Cell formatting isn't set to "Text". If it is, change it to general, or number, or something else, you'll have to edit the cell again before changes will take effect.

 

[TB]

I tried those before but they don't work, the cell doesn't update, it just registers the formula as a simple text entry. Summing formula works properly, so I don't know what could be the problem with multiplying.

On the Formulas tab, Calculation section, Click Calculate Options and make sure it's set to Automatic.

 

[Matej]

There's a number of reasons why that could happen, but start by checking the Cell formatting isn't set to "Text". If it is, change it to general, or number, or something else, you'll have to edit the cell again before changes will take effect.

On the Formulas tab, Calculation section, Click Calculate Options and make sure it's set to Automatic.

Thanks, problem solved. It didn't notice it would automatically format the cell to Text regardless of the formatting or entry, so I have fixed it now.

 

[ImaRobot]

Actually just had the same issue with my bosses sheet last week. I also found that highlighting the cells that are supposed to be numbers, you can right click and select "Convert to numbers." Wasn't a multiplication formula, but a VLookup that was looking for numbered cells, but they pulled as text so it just returned as #N/A. I was stumped for the whole day

 

[AOS-]

To reverse that, putting a ' in front of numbers or a formula will make Excel output that cell content as plain text string rather than converting it to Number or a formula to calculate....

but y'all may already know this.

 

[TenEightyOne]

To reverse that, putting a ' in front of numbers or a formula will make Excel output that cell content as plain text string rather than converting it to Number or a formula to calculate....

but y'all may already know this.

It does but you have to be careful using it if you're exporting delimited data from the sheet - the data will transport but the apostrophe can play havoc with scripts that read it.

 

[Rallywagon]

Ok, so, new excel question. I am working on a spreadsheet that has three sheets to it. What I am doing is is matching MAC address on sheet one with MAC vendors on sheet2 and IP address pulled from an ARP table on sheet3. I have no issues with the the vendor using =lookup. however I run into issues on the IP side because lookup will return the next lowest result if it cant find an exact match. Sometimes there are MAC address that dont align with an IP for whatever reason. when this happens the =lookup function will display the wrong IP. is there a way to make lookup return an NA when it cannot find an exact match? match and exact do not work since they return their own values rather than pull from a cell in the same row.
NAC implementation sucks btw....

 

[sesselpupser]

@Rallywagon can you post your formula and an example of one that works and one that doesn't? I don't really understand how this works.

 

[Rallywagon]

=lookup(D1,sheet3!D;D,sheet3!B:B)
The reference MAC address for the first sheet is D1. Sheet3 D;D is the column with the MAC pulled from the ARP table, sheet 3 B:B is the value returned to the formula, in my case the IP.
It works for the most part. The issue I have is that if the reference MAC from sheet 1 isnt on sheet 3, it defers to the next closest. Ie a MAC with the last 4 2dc8 that isnt in sheet3 D;D, will return with an IP for say a MAC with the last 4 2dc7. I need it to return a null or NA instead.

Edit, had to use ; instead of : because of smilies.

 

[ImaRobot]

=lookup(D1,sheet3!D;D,sheet3!B:B)
The reference MAC address for the first sheet is D1. Sheet3 D;D is the column with the MAC pulled from the ARP table, sheet 3 B:B is the value returned to the formula, in my case the IP.
It works for the most part. The issue I have is that if the reference MAC from sheet 1 isnt on sheet 3, it defers to the next closest. Ie a MAC with the last 4 2dc8 that isnt in sheet3 D;D, will return with an IP for say a MAC with the last 4 2dc7. I need it to return a null or NA instead.

Edit, had to use ; instead of : because of smilies.

=VLOOKUP(D1,sheet3!B*;D**,2,FALSE)

B*;D** - The star next to the B needs to be replaced with the first cell number from that column. Likewise for D, but instead the last cell number of the information that it's looking up. That should create a table for it to search through.

"2" is saying that the information returned is from column B, the 2nd column.

"FALSE" Has it pull an exact match.

This is the only way I got it to pull the exact information or returning it as an #N/A if no match is found, while messing with multiple sheets. I'm hoping I wrote it all out right lol.

EDIT: I ran into a little snag, so if that doesnt work I wont be able to check it out again until tomorrow.. when I'm at work again