Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Excel - Two formulas in one cell


(!)

CheesePlant's Avatar
CheesePlant CheesePlant is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Sep 2008
Experience: 1+2=3
04-Sep-2008, 05:05 AM #1
Solved: Excel - Two formulas in one cell
Hello,

I can't for the life of me remember how to put two formulas in one cell.

At the moment I have:

Cell 1 which contains a total:
=SUM(D68:T68)
Result is 5,245

Cell 2 shows the total as a percentage:
=$U68/U$83
Result is 9.16%

How do I put this into one cell to produce:
Total = 5,245 - 9.16%

Thank you in advance.
computerman29642's Avatar
Computer Specs
Member with 2,892 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Intermediate
04-Sep-2008, 08:05 AM #2
CheesePlant, welcome to the forum.

You could have a formula that looks like this....

Code:
=SUM(D68:T68) & " - " & $U68/U$83
CheesePlant's Avatar
CheesePlant CheesePlant is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Sep 2008
Experience: 1+2=3
04-Sep-2008, 09:05 AM #3
That was great but now I get 5245 - 0.0940257356

Is there a way I can put in a comma (5,245) and shorten the percentage into 2 decimal places (0.09)?

Thanks.
computerman29642's Avatar
Computer Specs
Member with 2,892 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Intermediate
04-Sep-2008, 09:09 AM #4
Would it be possible for you to attach a sample spreadsheet?
CheesePlant's Avatar
CheesePlant CheesePlant is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Sep 2008
Experience: 1+2=3
04-Sep-2008, 09:27 AM #5
Unfortunately the spreadsheet is quite complex and holds information I can't easily remove to freely hand it out.

I am using this code:

=" Total: " & SUM(D66:T66) & " - " & $U66/U$81 & "%"



SUM(D66:T66) is resulting in 5,245 and $U66/U$81 results in 0.09
computerman29642's Avatar
Computer Specs
Member with 2,892 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Intermediate
04-Sep-2008, 09:36 AM #6
Try this formula...

Code:
=FIXED(SUM(D68:T68),0) & " - " & ROUND($U68/U$83,2)
CheesePlant's Avatar
CheesePlant CheesePlant is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Sep 2008
Experience: 1+2=3
04-Sep-2008, 09:47 AM #7
Ah that's interesting. I've just created an example (please find it attached). Maybe you could have a look and advise?
Attached Files
File Type: xls example.xls (8.5 KB, 1770 views)
computerman29642's Avatar
Computer Specs
Member with 2,892 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Intermediate
04-Sep-2008, 09:52 AM #8
I took a look at the spreadsheet you attached. Please take a look at the revised spreadsheet I have attached.
Attached Files
File Type: xls example_revised.xls (17.5 KB, 3486 views)
CheesePlant's Avatar
CheesePlant CheesePlant is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Sep 2008
Experience: 1+2=3
04-Sep-2008, 09:54 AM #9
Thank you very much. That is perfect. I wish I completely understood it but I'll spend some time working it out.

Cheers!
computerman29642's Avatar
Computer Specs
Member with 2,892 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Intermediate
04-Sep-2008, 09:57 AM #10
You are welcome.

Is there anything I can try to clear up for you?
CheesePlant's Avatar
CheesePlant CheesePlant is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Sep 2008
Experience: 1+2=3
04-Sep-2008, 10:01 AM #11
Just one more problem, I've had to change it to match my spreadsheet which had more than 2 months details so I added a SUM:


=" Total: " & FIXED(SUM(D66:T66),0) & " - " & ROUND($U$81/SUM(D66:T66),2) & "% of total"

The last SUM(D66:T66) is causing me problems, im getting a higher %?
computerman29642's Avatar
Computer Specs
Member with 2,892 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Intermediate
04-Sep-2008, 10:05 AM #12
It would be better if I could see the problem. Could you attach another example?

When I use
Quote:
=" Total: " & FIXED(SUM(D66:T66),0) & " - " & ROUND($U$81/SUM(D66:T66),2) & "% of total"
it works fine for me.
CheesePlant's Avatar
CheesePlant CheesePlant is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Sep 2008
Experience: 1+2=3
04-Sep-2008, 10:09 AM #13
Sure, attached is a new version. Thanks.
Attached Files
File Type: xls example2.xls (18.5 KB, 588 views)
computerman29642's Avatar
Computer Specs
Member with 2,892 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Intermediate
04-Sep-2008, 10:22 AM #14
Take a look at the revised spreadsheet.
Attached Files
File Type: xls example_revised2.xls (21.5 KB, 1630 views)
CheesePlant's Avatar
CheesePlant CheesePlant is offline
Junior Member with 11 posts.
THREAD STARTER
 
Join Date: Sep 2008
Experience: 1+2=3
04-Sep-2008, 10:27 AM #15
Great thank you very much for your help
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑