formula to return a sentence by pulling information from different cells

[surveyorkyle]

hi all,

I'm trying to use a formula to make an auto populated sentence by pulling information from different cells.
I need the formula to return a "C-"(B12-E12)"RG"(B12), IF (b12-e12) is a negative number or a "F+" if (b1-e12) is a positive number.
b12=700
e12=695.5
sentence would= "C-4.5 RG (695.5)"

I've been trying to get this for awhile now but haven't had any luck. Any help would be appreciated.

Thanks,

Kyle

 

[skywriter]

Sorry, I had something in mind, but your post doesn't make sense to me.
You show "RG"(B12) and then in the finished sentence you show RG(695.5), but you state B12 is 700?

 

[surveyorkyle]

Thanks bruce!!!! I'm almost there.

I modified the formula slightly but im getting more then 1 decimal place in the subtracted cells. Does that need to be determined in the formula?

=if(b12-e12>0,"c" & (b12-e12) & " rg " & b12, "f+" & (b12-e12) & " rg " & b12)

is there a way to produce brackets around the last number in the sentence.

To make it look like this "f+0.3 rg (732.3)"

 

[cyrilbrd]

maybe something like =IF(B12-E12>0,"c" & (B12-E12) & " rg " &"("& B12&")", "f+" & (B12-E12) & " rg " &"("& B12&")")
although I am not not sure why you omitted the first logical test in your if :=if(b12-e12,0, which I changed to =if(b12-e12>0, you may modify to fit your query.

 

[surveyorkyle]

Sorry that was a typo but you must have gotten the response before i corrected it.
Thanks for the help.
Im still getting more then one decimal place. Do you have a suggestion how to fix that.

 

[cyrilbrd]

what do you get and what would you want to see instead? first value? second value? both, do you want zero decimal?
example: c4 rg (700) or c4.1 rg (699.8) ?

 

[surveyorkyle]

Im getting when i subtract b12 and e12 .10000000000000023. I only need one decimal place. I only have one decimal place in each of the cells its referencing. I want it to read "f+.1 rg (700.1)"

 

[cyrilbrd]

c4 rg (700) is =IF(B12-E12>0,"c" & ROUND(B12-E12,0) & " rg " &"("& ROUND(B12,0)&")", "f+" & ROUND(B12-E12,0) & " rg " &"("& ROUND(B12,0)&")")

c4.1 rg (699.8) is =IF(B12-E12>0,"c" & ROUND(B12-E12,1) & " rg " &"("& ROUND(B12,1)&")", "f+" & ROUND(B12-E12,1) & " rg " &"("& ROUND(B12,1)&")")

you may adjust to fit your model.

 

[surveyorkyle]

Perfect!!! Thats what i needed. You guys are the best. Thank you

kyle

 

[cyrilbrd]

Perfect!!! Thats what i needed. You guys are the best. Thank you

kyle

Most welcome. Thanks for the feedback.