calculations wrong

[reddcannon]

excel 16 on windows 10
I have several tabs on my sheet, here is my problem, on my sheet named Pricesheet, I have amount in column e14 calculate a14 *c14 = total in e14

c14 is =Parts!D7 comes from tab named parts and this is the field and =Parts!D7 says =C7*10%+C7
my return value keeps coming up 1204.90 answer needs to be exact 1205.12

Thanks so much in advance

 

[Joe4]

It is hard for us to debug if we don't know the exact value in each cell!

What exactly are your values in each of the cells in all the formulas you listed?
And don't rely on what you see when viewing the sheet. Go to each cell, and check the values in the formula bars.
(If you rely on what you see on the sheet, you will miss any rounding that may have occurred in cell formatting - your formulas run off the ACTUAL values in the cells, not their displayed values).

You may need to apply the ROUND formula to some of your cells to get things to work the way you want.

 

[reddcannon]

excel 16 on windows 10
I have several tabs on my sheet, here is my problem, on my sheet named Pricesheet, I have amount in column e14 calculate a14 *c14 = total in e14

c14 is =Parts!D7 comes from tab named parts and this is the field and =Parts!D7 says =C7*10%+C7
my return value keeps coming up 1204.90 answer needs to be exact 1205.12

Thanks so much in advance

excel 16 on windows 10
I have several tabs on my sheet, here is my problem, on my sheet named Pricesheet, I have amount in column e14 calculate a14 *c14 = total in e14

c14 is =Parts!D7 comes from tab named parts and this is the field and =Parts!D7 says =C7*10%+C7
my return value keeps coming up 1204.90 answer needs to be exact 1205.12

Thanks so much in advance

excel 16 on windows 10
I have several tabs on my sheet, here is my problem, on my sheet named Pricesheet, I have amount in column e14 calculate a14 *c14 = total in e14

c14 is =Parts!D7 comes from tab named parts and this is the field and =Parts!D7 says =C7*10%+C7
my return value keeps coming up 1204.90 answer needs to be exact 1205.12

Thanks so much in advance

a14 has =IF(Quotation!C2=6,"",Quotation!E25)
c14 has =Parts!D7
e14 has =IFERROR(IF(OR([@Quantity]="",[@[Unit Price]]=""),"",[@Quantity]*[@[Unit Price]]),"")

if I manually type in the amount on C14 then I get the right answer. But I really need it to go out to the parts tab and get the amounts

 

[Joe4]

No, I need to know that actual numeric values in the underlying source cells (as they really are, not as they appear).

Go to cell E25 on your Quotation sheet, change the number format, expanding out to 6 decimal places, and tell me what number you see in that cell.

Likewise, go to cell D7 on your Parts, change the number format, expanding out to 6 decimal places, and tell me what number you see in that cell.

 

[reddcannon]

No, I need to know that actual numeric values in the underlying source cells (as they really are, not as they appear).

Go to cell E25 on your Quotation sheet, change the number format, expanding out to 6 decimal places, and tell me what number you see in that cell.

Likewise, go to cell D7 on your Parts, change the number format, expanding out to 6 decimal places, and tell me what number you see in that cell.

on quotation sheet e25 is 100.000
on parts d7 is 10.7580

 

[Joe4]

on parts d7 is 10.7580

That is your problem right there.

It is imported to understand the difference between stored value and visible value and how Excel works.
Changing the display in the cell using Cell Formatting does NOT actually change the value in the cell, it just changes what is displayed.
You can see this if you click on the cell, and look what is shown in the formula bar.
What is shown in the formula bar is what Excel actually has stored, and this is the value that will be used in any calculation.
You can change the cell number format to any format that you like (showing as many decimals as you like), and it will have absolutely no affect on the calculation.

If you are showing two decimal places, and actually want to multiply by the value displayed, then use the ROUND function to round that value to the same number of decimal places you are displaying, i.e.

=a14*ROUND(c14,2)

 

[reddcannon]

That is your problem right there.

It is imported to understand the difference between stored value and visible value and how Excel works.
Changing the display in the cell using Cell Formatting does NOT actually change the value in the cell, it just changes what is displayed.
You can see this if you click on the cell, and look what is shown in the formula bar.
What is shown in the formula bar is what Excel actually has stored, and this is the value that will be used in any calculation.
You can change the cell number format to any format that you like (showing as many decimals as you like), and it will have absolutely no affect on the calculation.

If you are showing two decimal places, and actually want to multiply by the value displayed, then use the ROUND function to round that value to the same number of decimal places you are displaying, i.e.

=a14*ROUND(c14,2)

That fixed it, I am so sorry for being so dumb, thank you so so much

 

[Fluff]

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[Joe4]

That fixed it, I am so sorry for being so dumb, thank you so so much

No worries.

It is a common misconception that most of us have had at one time.
Just remember that Cell Formatting only affects appearance, not calculations.

 

[FGaxha]

Check the format Nr:
Flow the instruction as per attachment below.