formula to return a sentence by pulling information from different cells

surveyorkyle

New Member
Joined
Feb 7, 2011
Messages
6
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
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?
 
Last edited:

surveyorkyle

New Member
Joined
Feb 7, 2011
Messages
6
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?
Code:
=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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
 
Last edited:

surveyorkyle

New Member
Joined
Feb 7, 2011
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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) ?
 
Last edited:

surveyorkyle

New Member
Joined
Feb 7, 2011
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,024
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top