Excel 2010 formula show text and percentage

[Pgouevia]

Hello all,

First time questioner on this board.

Let's say I am putting my formula in A1 Sheet 1. The formula I have references cells on other sheets in the workbook. For example, the formula in A1 Sheet 1 reads ='Sheet2!'A1 and thus shows the number in A1 Sheet 2.

Ok easy. Now, suppose that number in A1 Sheet 2 is a percentage? It calculated a percentage from B1 and C1 of Sheet 2 (let's say A1 Sheet 2 has the formula =B1/C1 where B1=1 and C1=2, and since it is formatted as a percentage, A1 Sheet 2 shows 50% (because it is 1/2). Great. The concept is I can enter a numerator and denominator in B1 and C1 of Sheet 2 respectively, have a percentage calculate in A1 Sheet 2, and that percentage will show in A1 Sheet 1.

Ok hopefully you're following along. Now in A1Sheet1, I want to show the percentage in A1Sheet2 but I also want to show the percentage in A2Sheet2 with a space between them (row 2 in Sheet 2 has the same formula as row 1 but referencing row 2 instead... so =B2/C2 formatted as a percentage cell). Let's say the percentage in B1Sheet2 is 100%. I want A1 Sheet1 to show 50% 100%.

Ok, if you've gotten this far, this is the last piece. Let's say back on Sheet 2, I have names in column D. So the idea would be that D1 has the text apples and D2 has the text oranges. I want my A1Sheet1 cell to show apples: 50% oranges: 100%

And if it can be done, I want them to show on different lines of the same cell (like how you can write on a second line if you press alt+enter). So in one cell it would show:

apples: 50%

oranges: 100%



Can it be done?

My formula is obviously wrong but hopefully someone can help me. It would be something like ='Sheet2!D1&": "&'Sheet2!'A1&alt+enter&'Sheet2!'D2&": " &'Sheet2!'A2 now obviously alt+enter isn't correct but I'm not sure what else in doing wrong. The formula that is in there right now is pretty close, but it shows 0.5 and 1.0 instead of 50% and 100%.

The practical example is a man has 2 apples and 2 oranges. He wants to know what percentage of each he's sold. He can enter how much of each fruit he's sold on column B and the amount of fruit he started with in column C. Column A calculates for him the percentage sold, and column D shows the name of each fruit. Now on Sheet1 in box A1, his accountant wants to know how many apples and oranges he's sold but he only gave him one cell to put it in and the two fruits sold at differing percentages. This his dilemma.

It's 2:30am. I wonder if this will ever make sense to anyone but me.

 

[mole999]

concatenate is one way

or =C23&" "&D23

or

=C23&"% "&D23

 

[Peter_SSs]

Welcome to the MrExcel board!

It was a rather long-winded explanation but I think I understand.

You will need more than just concatenation as you have to re-create the percentage format in your formula cell. The TEXT() function is used to do that.

For your described example, try this in cell A1 of Sheet1

=Sheet2!D1&": "&TEXT(Sheet2!A1,"0%")&CHAR(10)&Sheet2!D2&": "&TEXT(Sheet2!A2,"0%")

You will also need to set 'Wrap Text' for that cell

 

[Pgouevia]

Thank you both! I thought concatenate was going to be an associated function but Peter jumped in with my formatting the percentage issue.

Peter, I used your formula and modified it to reference the appropriate cells in my document and it works perfectly. Thank you so very much!

Welcome to the MrExcel board!

It was a rather long-winded explanation but I think I understand.

You will need more than just concatenation as you have to re-create the percentage format in your formula cell. The TEXT() function is used to do that.

For your described example, try this in cell A1 of Sheet1

=Sheet2!D1&": "&TEXT(Sheet2!A1,"0%")&CHAR(10)&Sheet2!D2&": "&TEXT(Sheet2!A2,"0%")

You will also need to set 'Wrap Text' for that cell

 

[Peter_SSs]

Thank you both! I thought concatenate was going to be an associated function but Peter jumped in with my formatting the percentage issue.

Peter, I used your formula and modified it to reference the appropriate cells in my document and it works perfectly. Thank you so very much!

You are welcome. Glad it is what you wanted.