Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make it positive
Here's the situation, I have a problem that has 20 units purchased and
I receive a 7% discount for over 20 units purchased. Cost per unit is $52. Therefore, in the following If statement, the total would be 20 * $52 * .07 = 72.8 then take this total and subtract if from 20 * $52 which should equal $967.2 but I get a negative with my statement. I think it's easy but I don't see it, please advise...thanks... Dim foundRowNum As Integer Dim numberPurchasedStr As String Dim totalSpent As Currency Dim unitCostRow As Currency Dim discountRow As Double If (CInt(numberPurchasedStr) = Range("C" & foundRowNum).Value) Then totalSpent = CDbl(numberPurchasedStr) * unitCostRow * discountRow _ - CDbl(numberPurchasedStr) * unitCostRow |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make it positive
You formula is backwards. You are subtracting the 1040 from 72.8 rather than
subtracting 72.8 form 1040 Here is the easy fix: Sub AAA() Dim numberPurchasedStr As String Dim totalSpent As Currency Dim unitCostRow As Currency Dim discountRow As Double numberPurchasedStr = "20" unitCostRow = 52 discountRow = 0.07 totalSpent = -CDbl(numberPurchasedStr) _ * unitCostRow * discountRow _ + CDbl(numberPurchasedStr) * unitCostRow Debug.Print CDbl(numberPurchasedStr) _ * unitCostRow * discountRow, _ CDbl(numberPurchasedStr) * unitCostRow, _ totalSpent End Sub produces 72.8 1040 967.2 -- Regards, Tom Ogilvy " wrote: Here's the situation, I have a problem that has 20 units purchased and I receive a 7% discount for over 20 units purchased. Cost per unit is $52. Therefore, in the following If statement, the total would be 20 * $52 * .07 = 72.8 then take this total and subtract if from 20 * $52 which should equal $967.2 but I get a negative with my statement. I think it's easy but I don't see it, please advise...thanks... Dim foundRowNum As Integer Dim numberPurchasedStr As String Dim totalSpent As Currency Dim unitCostRow As Currency Dim discountRow As Double If (CInt(numberPurchasedStr) = Range("C" & foundRowNum).Value) Then totalSpent = CDbl(numberPurchasedStr) * unitCostRow * discountRow _ - CDbl(numberPurchasedStr) * unitCostRow |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make it positive
Aye, it was that simple...thanks a bunch...
On May 18, 2:57 pm, Tom Ogilvy wrote: You formula is backwards. You are subtracting the 1040 from 72.8 rather than subtracting 72.8 form 1040 Here is the easy fix: Sub AAA() Dim numberPurchasedStr As String Dim totalSpent As Currency Dim unitCostRow As Currency Dim discountRow As Double numberPurchasedStr = "20" unitCostRow = 52 discountRow = 0.07 totalSpent = -CDbl(numberPurchasedStr) _ * unitCostRow * discountRow _ + CDbl(numberPurchasedStr) * unitCostRow Debug.Print CDbl(numberPurchasedStr) _ * unitCostRow * discountRow, _ CDbl(numberPurchasedStr) * unitCostRow, _ totalSpent End Sub produces 72.8 1040 967.2 -- Regards, Tom Ogilvy " wrote: Here's the situation, I have a problem that has 20 units purchased and I receive a 7% discount for over 20 units purchased. Cost per unit is $52. Therefore, in the following If statement, the total would be 20 * $52 * .07 = 72.8 then take this total and subtract if from 20 * $52 which should equal $967.2 but I get a negative with my statement. I think it's easy but I don't see it, please advise...thanks... Dim foundRowNum As Integer Dim numberPurchasedStr As String Dim totalSpent As Currency Dim unitCostRow As Currency Dim discountRow As Double If (CInt(numberPurchasedStr) = Range("C" & foundRowNum).Value) Then totalSpent = CDbl(numberPurchasedStr) * unitCostRow * discountRow _ - CDbl(numberPurchasedStr) * unitCostRow |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtracting positive amts from negative and positive from positive | Excel Worksheet Functions | |||
make a positive number to negative | Excel Worksheet Functions | |||
positive value | Excel Programming | |||
Easy way to make negative data positive? | Charts and Charting in Excel | |||
Formula to make Negative Values Positive & Positive Values Negative? | Excel Discussion (Misc queries) |