Home

Formatting combined text and formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-762992%22%20slang%3D%22en-US%22%3EFormatting%20combined%20text%20and%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-762992%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20for%20calculating%20total%20hours%20worked%2C%20and%20am%20using%20a%20combined%20text%2Fformula%20formula%20to%20create%20the%20following%20result%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CP%3E%3CSTRONG%3ETotal%20%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%26nbsp%3BHrs%3C%2FSTRONG%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E4.506%20%2F%2072%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EBut%20I%20want%20to%20limit%20my%20formula%20result%20to%202%20decimal%20places.%20I%20have%20seen%20examples%20on%20how%20to%20format%20the%20output%20as%20hh%3Amm%20and%20as%20%25%2C%26nbsp%3B%20but%20not%20just%202%20decimal%20places.%3C%2FP%3E%3CP%3EMy%20formula%20is%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%22Total%20%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EHrs%20%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%26amp%3B(SUM(J2%3AJ100%2C%220.00%22))%26amp%3B%22%20%2F%20%22%26amp%3B((COUNTIF(L2%3AL100%2C1))*8)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20I%20thought%20the%20%220.00%22%20would%20set%20the%20output%20format%2C%20but%20it%20doesn't%20have%20any%20effect.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20am%20I%20missing%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-762992%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-763047%22%20slang%3D%22en-US%22%3ERe%3A%20Formatting%20combined%20text%20and%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-763047%22%20slang%3D%22en-US%22%3ESorry%2C%20I%20didn't%20spend%20enough%20time%20experimenting%20before%20I%20posted%20a%20question%3B%20but%20perhaps%20this%20will%20help%20someone%20else.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20correct%20formula%20is%3CBR%20%2F%3E%3D%22Total%3CBR%20%2F%3EHrs%20%22%3CBR%20%2F%3E%26amp%3B(TEXT(SUM(J2%3AJ100)%2C%220.00%22)%26amp%3B%22%20%2F%20%22%26amp%3B((COUNTIF(L2%3AL100%2C1))*8))%3CBR%20%2F%3Eand%20produces%3CBR%20%2F%3E%22Total%3CBR%20%2F%3EHrs%204.51%20%2F%2072%22%3CBR%20%2F%3Ewhich%20is%20what%20I%20wanted%20all%20along.%3C%2FLINGO-BODY%3E
CMI-JayK
New Contributor

I have a spreadsheet for calculating total hours worked, and am using a combined text/formula formula to create the following result:

Total
 Hrs  

4.506 / 72

But I want to limit my formula result to 2 decimal places. I have seen examples on how to format the output as hh:mm and as %,  but not just 2 decimal places.

My formula is:

="Total
Hrs "

&(SUM(J2:J100,"0.00"))&" / "&((COUNTIF(L2:L100,1))*8)

and I thought the "0.00" would set the output format, but it doesn't have any effect.

 

What am I missing?

1 Reply
Sorry, I didn't spend enough time experimenting before I posted a question; but perhaps this will help someone else.

The correct formula is
="Total
Hrs "
&(TEXT(SUM(J2:J100),"0.00")&" / "&((COUNTIF(L2:L100,1))*8))
and produces
"Total
Hrs 4.51 / 72"
which is what I wanted all along.