adding text to a formula
Posted by Karen on June 16, 2001 12:56 PM
I know this is probably really easy, but I just can't figure it out. I have a formula [=IF(SUM(M9:M28)=0," ",SUM(M9:M28))] that I want to add text to if the value is false. For example, the formula is calculating time values and if the answer is 0, I want the cell to be blank. If the answer is greater than 0, I want the cell to read the number and the word "hours". Any suggestions? Any help would be immensely appreciated.
Posted by Aladin Akyurek on June 16, 2001 1:16 PM
Assuming that your SUM formula results in
16:40 [ Is this the format that you have? ],
are you seeking to have this result to be:
16 hours and 40 minutes?
Aladin
Posted by Joe Was on June 16, 2001 11:12 PM
On sheet X where you need the data;
='Sheet y'!D9 & " and " & 'Sheet y'!E9
The trick is to reference the sheet name with a ! next to the Cell address or Range, like Sheet1! or 'Sheet1'! both work. The concatenation is done by space then "&" space with no quotes around the ampersand. You must add any formatting on your own!
If D9=123 and E9=Test
then, =D9 & E9 gives 123Test and
=D9 & "and" & E9 gives 123andTest
=D9 & " and " & E9 gives 123 and Test. JSW
Posted by Karen on June 17, 2001 3:57 PM
Aladin,
Yes that is the time format. I am doing an overtime expenditure sheet. Any help you can give would be greatly appreciated.
Karen
Posted by Aladin Akyurek on June 17, 2001 11:50 PM
Karen -- Is this what you want?
=IF(COUNT(M9:M28)>0,HOUR(SUM(M9:M28))&" hours and "&MINUTE(SUM(M9:M28))&" minute(s).","")
Aladin
===========
