Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding issue
Help....;o)
I have this formula : =IF(K10=1,I10*K10,K10-I10) and it keeps coming up with looks like some type of rounding. I have no condition formats set in these cells nor anything else, they are just formulas. Maybe someone knows of a better formula I can use. The thing is I have to have to make sure that the "total" column stays at zero dollars so that my sum function works at the end of the column. Thanks in advance. I K L UNIT PRICE QUANTITY * TOTAL** $10.00 -6 -$16.00 GRAND TOTALS: -$16.00 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding issue
My example is on here. The unit price is $10.00 and I subtract 6 and I'm
getting $-16.00, it's suppose to be -$60.00 but if I just put in 6 it calculates it correctly, so it must be the formula. Isn't that some type of rounding issue? No matter what you change the qty to it adds another dollar it looks like. I don't want the grand total to remain at zero, that would defeat my purpose of the calculation for my grand total. I have 30 line items if there isn't anything in the total field I need that to remain as zero $$'s and not get the #N/A error. "Sandy Mann" wrote: Kimo, What do you mean by: with looks like some type of rounding. Can you give an example. Also what do you mean by: of a better formula I can use. The thing is I have to have to make sure that the "total" column stays at zero dollars so that my sum function works at the end of the column. How can the Total remain at zero? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Kimo" wrote in message ... Help....;o) I have this formula : =IF(K10=1,I10*K10,K10-I10) and it keeps coming up with looks like some type of rounding. I have no condition formats set in these cells nor anything else, they are just formulas. Maybe someone knows of a better formula I can use. The thing is I have to have to make sure that the "total" column stays at zero dollars so that my sum function works at the end of the column. Thanks in advance. I K L UNIT PRICE QUANTITY * TOTAL** $10.00 -6 -$16.00 GRAND TOTALS: -$16.00 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding issue
I find it quite difficult to understand what it is that you are trying to
do. If you want $60 for a quantity of 6 and -$60 for a quantity of -6 then change the formula to: =IF(K10=1,I10*K10,K10*I10) If you want the Total cell to remain looking blank until a quantity is entered then use: =IF(K10="","",IF(K10=1,I10*K10,K10*I10)) To keep the Grand Total cell at the bottom of the 30 lines looking blank until you enter something then use: =IF(COUNT(L1:L31)=0,"",SUM(L1:L31)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Kimo" wrote in message ... My example is on here. The unit price is $10.00 and I subtract 6 and I'm getting $-16.00, it's suppose to be -$60.00 but if I just put in 6 it calculates it correctly, so it must be the formula. Isn't that some type of rounding issue? No matter what you change the qty to it adds another dollar it looks like. I don't want the grand total to remain at zero, that would defeat my purpose of the calculation for my grand total. I have 30 line items if there isn't anything in the total field I need that to remain as zero $$'s and not get the #N/A error. "Sandy Mann" wrote: Kimo, What do you mean by: with looks like some type of rounding. Can you give an example. Also what do you mean by: of a better formula I can use. The thing is I have to have to make sure that the "total" column stays at zero dollars so that my sum function works at the end of the column. How can the Total remain at zero? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Kimo" wrote in message ... Help....;o) I have this formula : =IF(K10=1,I10*K10,K10-I10) and it keeps coming up with looks like some type of rounding. I have no condition formats set in these cells nor anything else, they are just formulas. Maybe someone knows of a better formula I can use. The thing is I have to have to make sure that the "total" column stays at zero dollars so that my sum function works at the end of the column. Thanks in advance. I K L UNIT PRICE QUANTITY * TOTAL** $10.00 -6 -$16.00 GRAND TOTALS: -$16.00 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding issue
Neither 3 of those worked still rounded up. Though I figured it out, all I
had to do was change the format from number or general to text in the qty column, thanks for your help anyways. "Sandy Mann" wrote: I find it quite difficult to understand what it is that you are trying to do. If you want $60 for a quantity of 6 and -$60 for a quantity of -6 then change the formula to: =IF(K10=1,I10*K10,K10*I10) If you want the Total cell to remain looking blank until a quantity is entered then use: =IF(K10="","",IF(K10=1,I10*K10,K10*I10)) To keep the Grand Total cell at the bottom of the 30 lines looking blank until you enter something then use: =IF(COUNT(L1:L31)=0,"",SUM(L1:L31)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Kimo" wrote in message ... My example is on here. The unit price is $10.00 and I subtract 6 and I'm getting $-16.00, it's suppose to be -$60.00 but if I just put in 6 it calculates it correctly, so it must be the formula. Isn't that some type of rounding issue? No matter what you change the qty to it adds another dollar it looks like. I don't want the grand total to remain at zero, that would defeat my purpose of the calculation for my grand total. I have 30 line items if there isn't anything in the total field I need that to remain as zero $$'s and not get the #N/A error. "Sandy Mann" wrote: Kimo, What do you mean by: with looks like some type of rounding. Can you give an example. Also what do you mean by: of a better formula I can use. The thing is I have to have to make sure that the "total" column stays at zero dollars so that my sum function works at the end of the column. How can the Total remain at zero? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Kimo" wrote in message ... Help....;o) I have this formula : =IF(K10=1,I10*K10,K10-I10) and it keeps coming up with looks like some type of rounding. I have no condition formats set in these cells nor anything else, they are just formulas. Maybe someone knows of a better formula I can use. The thing is I have to have to make sure that the "total" column stays at zero dollars so that my sum function works at the end of the column. Thanks in advance. I K L UNIT PRICE QUANTITY * TOTAL** $10.00 -6 -$16.00 GRAND TOTALS: -$16.00 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rounding issue
"Kimo" wrote in message
... Neither 3 of those worked still rounded up. Though I figured it out, all I had to do was change the format from number or general to text in the qty column, thanks for your help anyways I still don't understand it but as long as you are sorted now I'm happy -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
Excel Rounding Issue | Excel Discussion (Misc queries) | |||
Sum Issue Help! | Excel Discussion (Misc queries) | |||
Rounding issue with Goal Seek for IRR | Excel Worksheet Functions | |||
Another rounding issue | Excel Worksheet Functions |