Converting Excel Formula into TSQL

• Ronnie Rahman

  Say Hey Kid

  Points: 674

  More actions

  July 18, 2018 at 9:46 am

  #373594

  Hello Dear Friends,

  I need a help with Converting an Excel Formula below into TSQL

  Excel Formula: 
  

  ==15.42*(1-(125-100)/100)

  
  Expected Correct Result: 15.17

  I have tried to in TSQL
  SELECT 15.42 * (1- (125 - 100 )/1)/10.0
  Incorrect Result : 
  (No column name)
  -37.008000

  Any help will be much appreciated.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 18, 2018 at 10:03 am

  #1998030

  Ronnie Rahman - Wednesday, July 18, 2018 9:46 AM

  Hello Dear Friends,

  I need a help with Converting an Excel Formula below into TSQL

  Excel Formula: 
  

  =15.42-(125-100)/100

  
  Expected Correct Result: 15.17

  I have tried to in TSQL
  SELECT 15.42 * (1- (125 - 100 )/1)/10.0
  Incorrect Result : 
  (No column name)
  -37.008000

  Any help will be much appreciated.

  Never mind, that was just guess without looking first.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 18, 2018 at 10:06 am

  #1998031

  Ronnie Rahman - Wednesday, July 18, 2018 9:46 AM

  Hello Dear Friends,

  I need a help with Converting an Excel Formula below into TSQL

  Excel Formula: 
  

  =15.42-(125-100)/100

  
  Expected Correct Result: 15.17

  I have tried to in TSQL
  SELECT 15.42 * (1- (125 - 100 )/1)/10.0
  Incorrect Result : 
  (No column name)
  -37.008000

  Any help will be much appreciated.

  Your formula is wrong, try this:

  SELECT 15.42-(125-100)/100

• Ronnie Rahman

  Say Hey Kid

  Points: 674

  More actions

  July 18, 2018 at 10:10 am

  #1998032

  Lynn Pettis - Wednesday, July 18, 2018 10:06 AM

  Ronnie Rahman - Wednesday, July 18, 2018 9:46 AM

  Hello Dear Friends,

  I need a help with Converting an Excel Formula below into TSQL

  Excel Formula: 
  

  =15.42-(125-100)/100

  
  Expected Correct Result: 15.17

  I have tried to in TSQL
  SELECT 15.42 * (1- (125 - 100 )/1)/10.0
  Incorrect Result : 
  (No column name)
  -37.008000

  Any help will be much appreciated.

  Your formula is wrong, try this:

  SELECT 15.42-(125-100)/100

  Hello

  Thank you for your quick help
  SELECT  15.42-(-(125-100)/)/100
  This formula gives me the incorrect result, expecting result 15.17
  (No column name)
  3.855000

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 18, 2018 at 10:12 am

  #1998033

  Ronnie Rahman - Wednesday, July 18, 2018 10:10 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:06 AM

  Ronnie Rahman - Wednesday, July 18, 2018 9:46 AM

  Hello Dear Friends,

  I need a help with Converting an Excel Formula below into TSQL

  Excel Formula: 
  

  =15.42-(125-100)/100

  
  Expected Correct Result: 15.17

  I have tried to in TSQL
  SELECT 15.42 * (1- (125 - 100 )/1)/10.0
  Incorrect Result : 
  (No column name)
  -37.008000

  Any help will be much appreciated.

  Your formula is wrong, try this:

  SELECT 15.42-(125-100)/100

  Hello

  Thank you for your quick help
  SELECT  15.42-(-(125125--100100)/)/100100
  This formula gives me the incorrect result, expecting result 15.17
  (No column name)
  3.855000

  I run this:

  SELECT 15.42-(125-100)/100 and I get 15.17.
  What you posted isn't even close to this.
  

• Ronnie Rahman

  Say Hey Kid

  Points: 674

  More actions

  July 18, 2018 at 10:20 am

  #1998034

  Lynn Pettis - Wednesday, July 18, 2018 10:12 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:10 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:06 AM

  Ronnie Rahman - Wednesday, July 18, 2018 9:46 AM

  Hello Dear Friends,

  I need a help with Converting an Excel Formula below into TSQL

  Excel Formula: 
  

  =15.42-(125-100)/100

  
  Expected Correct Result: 15.17

  I have tried to in TSQL
  SELECT 15.42 * (1- (125 - 100 )/1)/10.0
  Incorrect Result : 
  (No column name)
  -37.008000

  Any help will be much appreciated.

  Your formula is wrong, try this:

  SELECT 15.42-(125-100)/100

  Hello

  Thank you for your quick help
  SELECT  15.42-(-(125125--100100)/)/100100
  This formula gives me the incorrect result, expecting result 15.17
  (No column name)
  3.855000

  I run this:

  SELECT 15.42-(125-100)/100 and I get 15.17.
  What you posted isn't even close to this.
  

  My apologies for the incorrect SQL formula
  Excel Formula: 

  =15.42 * (125-100)/100

  Expected Correct Result: 15.17
  15.42 needs to multiply with the result of (125-100)/100 as per the Excel formula.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 18, 2018 at 10:22 am

  #1998035

  Ronnie Rahman - Wednesday, July 18, 2018 10:20 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:12 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:10 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:06 AM

  Ronnie Rahman - Wednesday, July 18, 2018 9:46 AM

  Hello Dear Friends,

  I need a help with Converting an Excel Formula below into TSQL

  Excel Formula: 
  

  =15.42-(125-100)/100

  
  Expected Correct Result: 15.17

  I have tried to in TSQL
  SELECT 15.42 * (1- (125 - 100 )/1)/10.0
  Incorrect Result : 
  (No column name)
  -37.008000

  Any help will be much appreciated.

  Your formula is wrong, try this:

  SELECT 15.42-(125-100)/100

  Hello

  Thank you for your quick help
  SELECT  15.42-(-(125125--100100)/)/100100
  This formula gives me the incorrect result, expecting result 15.17
  (No column name)
  3.855000

  I run this:

  SELECT 15.42-(125-100)/100 and I get 15.17.
  What you posted isn't even close to this.
  

  My apologies for the incorrect SQL formula
  Excel Formula: 

  =15.42 * (125-100)/100

  Expected Correct Result: 15.17
  15.42 needs to multiply with the result of (125-100)/100 as per the Excel formula.

  Okay, back to what I original thought, integer arithmetic:

  SELECT 15.42-(125.-100.)/100.

• Ronnie Rahman

  Say Hey Kid

  Points: 674

  More actions

  July 18, 2018 at 10:30 am

  #1998036

  Lynn Pettis - Wednesday, July 18, 2018 10:22 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:20 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:12 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:10 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:06 AM

  Ronnie Rahman - Wednesday, July 18, 2018 9:46 AM

  Hello Dear Friends,

  I need a help with Converting an Excel Formula below into TSQL

  Excel Formula: 
  

  =15.42-(125-100)/100

  
  Expected Correct Result: 15.17

  I have tried to in TSQL
  SELECT 15.42 * (1- (125 - 100 )/1)/10.0
  Incorrect Result : 
  (No column name)
  -37.008000

  Any help will be much appreciated.

  Your formula is wrong, try this:

  SELECT 15.42-(125-100)/100

  Hello

  Thank you for your quick help
  SELECT  15.42-(-(125125--100100)/)/100100
  This formula gives me the incorrect result, expecting result 15.17
  (No column name)
  3.855000

  I run this:

  SELECT 15.42-(125-100)/100 and I get 15.17.
  What you posted isn't even close to this.
  

  My apologies for the incorrect SQL formula
  Excel Formula: 

  =15.42 * (125-100)/100

  Expected Correct Result: 15.17
  15.42 needs to multiply with the result of (125-100)/100 as per the Excel formula.

  Okay, back to what I original thought, integer arithmetic:

  SELECT 15.42-(125.-100.)/100.

  I know this might make you upset, I have been given a wrong Excel formula from the beginning - I apologise for this.

  The correct Excel formula is =15.42*(1-(125-100)/100) and the expected result is 11.57

  How do you convert above formula to SQL to get  11.57?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 18, 2018 at 10:34 am

  #1998039

  Ronnie Rahman - Wednesday, July 18, 2018 10:30 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:22 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:20 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:12 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:10 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:06 AM

  Ronnie Rahman - Wednesday, July 18, 2018 9:46 AM

  Hello Dear Friends,

  I need a help with Converting an Excel Formula below into TSQL

  Excel Formula: 
  

  =15.42-(125-100)/100

  
  Expected Correct Result: 15.17

  I have tried to in TSQL
  SELECT 15.42 * (1- (125 - 100 )/1)/10.0
  Incorrect Result : 
  (No column name)
  -37.008000

  Any help will be much appreciated.

  Your formula is wrong, try this:

  SELECT 15.42-(125-100)/100

  Hello

  Thank you for your quick help
  SELECT  15.42-(-(125125--100100)/)/100100
  This formula gives me the incorrect result, expecting result 15.17
  (No column name)
  3.855000

  I run this:

  SELECT 15.42-(125-100)/100 and I get 15.17.
  What you posted isn't even close to this.
  

  My apologies for the incorrect SQL formula
  Excel Formula: 

  =15.42 * (125-100)/100

  Expected Correct Result: 15.17
  15.42 needs to multiply with the result of (125-100)/100 as per the Excel formula.

  Okay, back to what I original thought, integer arithmetic:

  SELECT 15.42-(125.-100.)/100.

  I know this might make you upset, I have been given a wrong Excel formula from the beginning - I apologise for this.

  The correct Excel formula is =15.42*(1-(125-100)/100) and the expected result is 11.57

  How do you convert above formula to SQL to get  11.57?

  Put a decimal point after EACH of the integer values, or at least after the last 100 in your formula.  In SQL SELECT 99/100 will return 0 as the result.

• Ronnie Rahman

  Say Hey Kid

  Points: 674

  More actions

  July 18, 2018 at 10:40 am

  #1998042

  Lynn Pettis - Wednesday, July 18, 2018 10:34 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:30 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:22 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:20 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:12 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:10 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:06 AM

  Ronnie Rahman - Wednesday, July 18, 2018 9:46 AM

  Hello Dear Friends,

  I need a help with Converting an Excel Formula below into TSQL

  Excel Formula: 
  

  =15.42-(125-100)/100

  
  Expected Correct Result: 15.17

  I have tried to in TSQL
  SELECT 15.42 * (1- (125 - 100 )/1)/10.0
  Incorrect Result : 
  (No column name)
  -37.008000

  Any help will be much appreciated.

  Your formula is wrong, try this:

  SELECT 15.42-(125-100)/100

  Hello

  Thank you for your quick help
  SELECT  15.42-(-(125125--100100)/)/100100
  This formula gives me the incorrect result, expecting result 15.17
  (No column name)
  3.855000

  I run this:

  SELECT 15.42-(125-100)/100 and I get 15.17.
  What you posted isn't even close to this.
  

  My apologies for the incorrect SQL formula
  Excel Formula: 

  =15.42 * (125-100)/100

  Expected Correct Result: 15.17
  15.42 needs to multiply with the result of (125-100)/100 as per the Excel formula.

  Okay, back to what I original thought, integer arithmetic:

  SELECT 15.42-(125.-100.)/100.

  I know this might make you upset, I have been given a wrong Excel formula from the beginning - I apologise for this.

  The correct Excel formula is =15.42*(1-(125-100)/100) and the expected result is 11.57

  How do you convert above formula to SQL to get  11.57?

  Put a decimal point after EACH of the integer values, or at least after the last 100 in your formula.  In SQL SELECT 99/100 will return 0 as the result.

  Thank you so much for your help finally got it to work. Result as expected 11.57

  select cast(15.42*(1.0-((125-100)/100.00)) as decimal(9,2))

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  July 18, 2018 at 11:32 am

  #1998057

  Ronnie Rahman - Wednesday, July 18, 2018 10:40 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:34 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:30 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:22 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:20 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:12 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:10 AM

  Lynn Pettis - Wednesday, July 18, 2018 10:06 AM

  Ronnie Rahman - Wednesday, July 18, 2018 9:46 AM

  Hello Dear Friends,

  I need a help with Converting an Excel Formula below into TSQL

  Excel Formula: 
  

  =15.42-(125-100)/100

  
  Expected Correct Result: 15.17

  I have tried to in TSQL
  SELECT 15.42 * (1- (125 - 100 )/1)/10.0
  Incorrect Result : 
  (No column name)
  -37.008000

  Any help will be much appreciated.

  Your formula is wrong, try this:

  SELECT 15.42-(125-100)/100

  Hello

  Thank you for your quick help
  SELECT  15.42-(-(125125--100100)/)/100100
  This formula gives me the incorrect result, expecting result 15.17
  (No column name)
  3.855000

  I run this:

  SELECT 15.42-(125-100)/100 and I get 15.17.
  What you posted isn't even close to this.
  

  My apologies for the incorrect SQL formula
  Excel Formula: 

  =15.42 * (125-100)/100

  Expected Correct Result: 15.17
  15.42 needs to multiply with the result of (125-100)/100 as per the Excel formula.

  Okay, back to what I original thought, integer arithmetic:

  SELECT 15.42-(125.-100.)/100.

  I know this might make you upset, I have been given a wrong Excel formula from the beginning - I apologise for this.

  The correct Excel formula is =15.42*(1-(125-100)/100) and the expected result is 11.57

  How do you convert above formula to SQL to get  11.57?

  Put a decimal point after EACH of the integer values, or at least after the last 100 in your formula.  In SQL SELECT 99/100 will return 0 as the result.

  Thank you so much for your help finally got it to work. Result as expected 11.57

  select cast(15.42*(1.0-((125-100)/100.00)) as decimal(9,2))

  The question now is.... do you understand why it now works and didn't before?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  July 18, 2018 at 11:36 am

  #1998061

  Ronnie Rahman - Wednesday, July 18, 2018 10:40 AM

  Thank you so much for your help finally got it to work. Result as expected 11.57

  select cast(15.42*(1.0-((125-100)/100.00)) as decimal(9,2))

  A simpler version would be
  SELECT 11.57
  🙂

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Ronnie Rahman

  Say Hey Kid

  Points: 674

  More actions

  July 18, 2018 at 11:39 am

  #1998063

  Phil Parkin - Wednesday, July 18, 2018 11:36 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:40 AM

  Thank you so much for your help finally got it to work. Result as expected 11.57

  select cast(15.42*(1.0-((125-100)/100.00)) as decimal(9,2))

  A simpler version would be
  SELECT 11.57
  🙂

  haha... I know and I wish.

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  July 18, 2018 at 12:03 pm

  #1998074

  Ronnie Rahman - Wednesday, July 18, 2018 11:39 AM

  Phil Parkin - Wednesday, July 18, 2018 11:36 AM

  Ronnie Rahman - Wednesday, July 18, 2018 10:40 AM

  Thank you so much for your help finally got it to work. Result as expected 11.57

  select cast(15.42*(1.0-((125-100)/100.00)) as decimal(9,2))

  A simpler version would be
  SELECT 11.57
  🙂

  haha... I know and I wish.

  You probably know this but need to make sure... What Phil means is that if you're not using anything but literal values, why not just use the literal value?  What value will be a variable value in your formula?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)