Multiplication is not correct

H

Helloitsme

New Member
Joined
Feb 19, 2021
Messages
28
Office Version
  1. 365
Platform
  1. Windows
Hi, I don't understand why my formula is showing an incorrect number in H12-H13 but H14-H15 is correct. They all use the same formula by looking up a value from the table, in another sheet, then performing a multiplication, some additions, then another multiplication to convert the number from Euro to my local currency. This MUST be done in this order.

Kollektion.xlsx
GH
1224.4924.34
1370.5826.74
1471.76897.02
15 1025.00
Overview
Cell Formulas
RangeFormula
G12:G15G12=IFERROR(VLOOKUP($F12,'Stock Perfume'!$E:$R,12,FALSE),"")
H12:H15H12=IFERROR(($G12*1.247+8.15+2*7.45),$I12)
Named Ranges
NameRefers ToCells
'Stock Perfume'!_FilterDatabase='Stock Perfume'!$E$4:$R$56087G12:G15
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J6:J5000,H6:H500Expression=IF($A6="";TRUE;FALSE)textYES
J6:J5000,H6:H500Cell Value<$I6-150textNO
J6:J5000,H6:H500Cell Value>$I6+10textNO


The VLOOKUP is returning the correct data, but when i multiply by 1,247 (24,7%), the data returned is significantly lower than the start value. Any help is appreciated!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Try changing your formulas to:

Excel Formula: 
=IFERROR(VLOOKUP($F12,'Stock Perfume'!$E:$R,12,FALSE)+0,"")

Excel Formula: 
=IFERROR(($G12*1.247+8.15+2*7.45),$I12)+0
 
Upvote 0
Sufiyan97 said:
Try changing your formulas to:

Excel Formula: 
=IFERROR(VLOOKUP($F12,'Stock Perfume'!$E:$R,12,FALSE)+0,"")

Excel Formula: 
=IFERROR(($G12*1.247+8.15+2*7.45),$I12)+0
Click to expand...

Thanks for the response! It doesn't seem to help, but returns different (although incorrect) data in the H column:

Kollektion.xlsx
HI
121.03402777824.34
132.95694444426.74
14 897.02
15 1025.00
Overview
Cell Formulas
RangeFormula
H12:H15H12=IFERROR(VLOOKUP($F12,'Stock Perfume'!$E:$R,12,FALSE)+0,"")
I12:I15I12=IFERROR(($G12*1.247+8.15+2*7.45),$J12)+0
Named Ranges
NameRefers ToCells
'Stock Perfume'!_FilterDatabase='Stock Perfume'!$E$4:$R$56087H12:H15
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K6:K5000,I6:I500Expression=IF($A6="";TRUE;FALSE)textYES
K6:K5000,I6:I500Cell Value<$J6-150textNO
K6:K5000,I6:I500Cell Value>$J6+10textNO
 
Upvote 0
In post #1 you have posted date from column G and H

and in post #3 you have posted data from column H & I

Can you use formulas in the data you have posted in post #1
 
Upvote 0
Sufiyan97 said:
In post #1 you have posted date from column G and H

and in post #3 you have posted data from column H & I

Can you use formulas in the data you have posted in post #1
Click to expand...
I had inserted a column somewhere that's not related sorry. Here's the same data as before in the columns from the #1 post:

Kollektion.xlsx
GH
121.03402777824.34
132.95694444426.74
14 897.02
15 1025.00
Overview
Cell Formulas
RangeFormula
G12:G15G12=IFERROR(VLOOKUP($F12,'Stock Perfume'!$E:$R,12,FALSE)+0,"")
H12:H15H12=IFERROR(($G12*1.247+8.15+2*7.45),$I12)+0
Named Ranges
NameRefers ToCells
'Stock Perfume'!_FilterDatabase='Stock Perfume'!$E$4:$R$56087G12:G15
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J6:J5000,H6:H500Expression=IF($A6="";TRUE;FALSE)textYES
J6:J5000,H6:H500Cell Value<$I6-150textNO
J6:J5000,H6:H500Cell Value>$I6+10textNO
 
Upvote 0
We seem to have a very different idea of what correct looks like.
It looks to me that all your Column H formulas are erroring out and you are just returning the Column I value.
Try putting =Value(G12) in a cell and tell us what it returns.

If column G doesn't have any unusual characters in it, then your formula produces the results below.

Book3
GHI
11ValueFormulaDefault (error) Value
1224.4953.5924.34
1370.58111.0626.74
1471.76112.53897.02
15 1,025.001,025.00
Sheet1
Cell Formulas
RangeFormula
H12:H15H12=IFERROR(($G12*1.247+8.15+2*7.45),$I12)
G15G15=IFERROR(VLOOKUP($F15,'Stock Perfume'!$E:$R,12,FALSE),"")
 
Upvote 0
Alex Blakenburg said:
We seem to have a very different idea of what correct looks like.
It looks to me that all your Column H formulas are erroring out and you are just returning the Column I value.
Try putting =Value(G12) in a cell and tell us what it returns.

If column G doesn't have any unusual characters in it, then your formula produces the results below.

Book3
GHI
11ValueFormulaDefault (error) Value
1224.4953.5924.34
1370.58111.0626.74
1471.76112.53897.02
15 1,025.001,025.00
Sheet1
Cell Formulas
RangeFormula
H12:H15H12=IFERROR(($G12*1.247+8.15+2*7.45),$I12)
G15G15=IFERROR(VLOOKUP($F15,'Stock Perfume'!$E:$R,12,FALSE),"")
Click to expand...
The column H formulas is not returning the value from the I column. But you may be right regarding the column G unusual characters, as the =VALUE(G12) returns 1,034027778. I'm suspecting the commas to be the problem, as in my country we'd write 24,49 instead of 24.49. Could this be the issue?

Kollektion.xlsx
GHI
1224.4924.34199
1370.5826.74352
1471.76897.02897.02
15 1025.001025
Overview
Cell Formulas
RangeFormula
G12:G15G12=IFERROR(VLOOKUP($F12,'Stock Perfume'!$E:$R,12,FALSE),"")
H12:H15H12=IFERROR(($G12*1.247+8.15+2*7.45),$I12)
Named Ranges
NameRefers ToCells
'Stock Perfume'!_FilterDatabase='Stock Perfume'!$E$4:$R$56087G12:G15
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J6:J5000,H6:H500Expression=IF($A6="";TRUE;FALSE)textYES
J6:J5000,H6:H500Cell Value<$I6-150textNO
J6:J5000,H6:H500Cell Value>$I6+10textNO
 
Upvote 0
Can you try this in G12
Excel Formula: 
=IFERROR(VALUE(SUBSTITUTE(VLOOKUP($F12,'Stock Perfume'!$E:$R,12,FALSE),".",",")),"")
 
Upvote 0
Solution
Alex Blakenburg said:
Can you try this in G12
Excel Formula: 
=IFERROR(VALUE(SUBSTITUTE(VLOOKUP($F12,'Stock Perfume'!$E:$R,12,FALSE),".",",")),"")
Click to expand...
You're an absolute genius. The substitute between . and , worked with wrapping the calculation correctly. Thank you so much!

Kollektion.xlsx
GHI
1224.49303.13199
1370.58731.32352
1471.76742.28897.02
15 1025.001025
Overview
Cell Formulas
RangeFormula
G12:G15G12=IFERROR(VALUE(SUBSTITUTE(VLOOKUP($F12,'Stock Perfume'!$E:$R,12,FALSE),".",",")),"")
H12:H15H12=IFERROR(($G12*1.247+8.15+2)*7.45,$I12)
Named Ranges
NameRefers ToCells
'Stock Perfume'!_FilterDatabase='Stock Perfume'!$E$4:$R$56087G12:G15
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J6:J5000,H6:H500Expression=IF($A6="";TRUE;FALSE)textYES
J6:J5000,H6:H500Cell Value<$I6-150textNO
J6:J5000,H6:H500Cell Value>$I6+10textNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

Romano_odK
  • Solved
How to remove al line with VBA when the values is "OK"
Replies
9
Views
259
Peter_SSs
Peter_SSs
K
  • Question
Roster lookup table - v/h/x lookup?
Replies
2
Views
205
Kariba
K
N
  • Question
Count - specific consecutive order
Replies
6
Views
541
Peter_SSs
Peter_SSs
T
  • Solved
Conditional Formatting of Green vs. Green/White, etc; Diagonal Border Not Possible
2
Replies
11
Views
776
MARK858
MARK858
E
  • Solved
Spilled formula to return the min of a reducing range
Replies
3
Views
106
Fluff
Fluff

Forum statistics

Threads
1,215,425
Messages
6,124,824
Members
449,190
Latest member
rscraig11

Share this page

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
Back
Top