Novice formula question

[Guest]

I'm creating a simple spreadsheet to track results BEFORE & AFTER for
students coming through a family fitness program.

Cell C26 is the difference between C22 & C24. If the SUM is greater than 0
is there a formula that could print the word LOST to the right of the sum?

Obviously I need the word GAINED if the sum is less than zero.

Thanks!!

 

[Guest]

Try this formula:

=IF(C26=0,"",IF(C26>0,"Lost",IF(C26<0,"Gained")))

If C26 = 0 the cell displays nothing, if less than 0 "Gained" and if > 0
"Lost"

 

[Sandy Mann]

=IF(C26<0,"Lost",IF(C26>0,"Gained","Still the same"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thanks Kevin: The formula works. I just need to add another column. It's
not possible for the formula to work in the same cell as the original
calculation - correct?

 

[Sandy Mann]

Assuming that C22 is the "Before" cell try:

=C22-C24&" "&IF(C22-C24>0,"Lbs Lost",IF(C22-C24<0,"Lbs Gained","No Change"))


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thank you Sandy - that's exactly what I needed. Thanks to you too Kevin, I
always add a couple of skills when I "chat" with the "experts".

 

[Sandy Mann]

Not so good. It returns negative figures try:

=ABS(C22-C24)&" "&IF(C22-C24>0,"Lbs Lost",IF(C22-C24<0,"Lbs Gained","No
Change"))

If you want to go further and have no s at the end of Lbs if the answer is 1
then use:

=ABS(C22-C24)&" "&IF(C22-C24>0,"Lb"&IF(C22-C24>1,"s","")&"
Lost",IF(C22-C24<0,"Lb"&IF(C22-C24<-1,"s","")&" Gained","No Change"))



--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Pete_UK]

Well, that seems to be dotting the i's and crossing the t's, Sandy
<bg>, although you will get:

0 No Change

whereas I would have thought just:

No Change

would be better.

Pete

 

[Sandy Mann]

Good point. Then how about:

=IF(C22-C24=0,"No Change",(ABS(C22-C24))&" "&IF(C22-C24>0,"Lbs Lost","Lbs
Gained"))

and:

=IF(C22-C24=0,"No Change",(ABS(C22-C24)&"
"&IF(C22-C24>0,"Lb"&IF(C22-C24>1,"s","")&"
Lost",IF(C22-C24<0,"Lb"&IF(C22-C24<-1,"s","")&" Gained"))))

Not exhaustively tested because it is past this old man's bedtime.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Pete_UK]

Not quite my bedtime yet ...

I would suggest:

=IF(C22-C24=0,"No Change",ABS(C22-C24)&" Lb"&IF(ABS(C22-
C24)>1,"s","")&IF(C22-C24>0," Lost"," Gained"))

Pete

 

[Sandy Mann]

Not quite my bedtime yet ...

At 1:24 am? Ah! Those were the days. <g>

Yes, I like it but it may be all academic because Denise e-mailed me at
mailinator.com, which is a spam trap so I almost never check it for non-spam
e-mails, and said that she was using Body Mass Index. Being a couch potato
that never occurred to me.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk