assistance using text and forumula

**twiggywales** · 02-09-2012, 06:53 AM

hi

i am trying to add some text in to a calculation however i cant seem to figure it out.

basically i have the cell to check 1 cell against another cell for a value. if it fails the requirements then i want the output to be (x1)@(x2)
where
x1 = (number from cell)
@ = Text
x2 = (number-(number*number))

so far i have this

=IF(F2>=B2,(C2-E2),IF(B2>D2,"",(C2-(A2*B2))))

for the last false statment (in bold) i want it to read (B2)@(C2-(A2*B2)

does that make sense?

**Pete_UK** · 02-09-2012, 06:56 AM

Try this:

=IF(F2>=B2,(C2-E2),IF(B2>D2,"",B2 & " @ " & (C2-(A2*B2))))

Hope this helps.

Pete

EDIT: Actually, if those numeric values could be fractional, you might consider this variation:

=IF(F2>=B2,(C2-E2),IF(B2>D2,"",TEXT(B2,"0.0") & " @ " & TEXT(C2-(A2*B2),"0.0")))

which will give you one decimal place only.

**twiggywales** · 02-09-2012, 07:14 AM

ok that has added what i need.

can you explain how this works for me so i dont have to ask questions like this again

also i assume that if i want to do saem with the fist statement (in bold below)

=IF(F2>=B2,(C2-E2),IF(B2>D2,"",TEXT(B2,"0.0") & " @ " & TEXT(C2-(A2*B2),"0.0")))

i would do this

=IF(F2>=B2,TEXT(B2,"0") & " @ " & TEXT((C2-E2),"0"),IF(B2>D2,"",TEXT(B2,"0") & " @ " & TEXT(C2-(A2*B2),"0")))

**Pete_UK** · 02-09-2012, 07:35 AM

Well, the easiest thing is to try it out, and that seems to me to do what you want.

As for how it works, the TEXT function converts numeric values into strings, and you can specify the format that you want those values to appear in using a Format string. It is very useful for dates, for example, where you could have this:

="Today's date is : "&TEXT(TODAY(),"dd mmm yyyy")

See what happens if you just have:

="Today's date is : "&TODAY()

Hope this helps.

Pete

**twiggywales** · 02-09-2012, 07:40 AM

thanks for this think i am getting the hang of this.

there are so many functions that i have never even looked at.

**Pete_UK** · 02-09-2012, 07:47 AM

You're welcome. Yeah, there are a load of functions that I've never used also.

Perhaps you can mark your thread as Solved, and maybe click on the star icon on any post that has helped you.

Pete