excel 2010 - if statement with % deduction in formula

hi experts, could you please assist with the following formula

=IF(D82="","",D82*E82*G82/100-F82)

what im trying to do here is;
if d82 = nothing then put in nothing, otherwise d82xg82/100-f82

ok so heres what are in these cells
d82 has 6.00 x
e82 has 2.40 =
f82 has 14.40-
g82 has 10%

the result to show in cell h82

so im looking for a formula to deduct 10% in this case but it wont always be 10% i will change this all the time so thats why its what ever i enter into the cell for the %.
please assist

Also, for some reason i cant print these solutions for some reason, there is 1 icon in experts exchange but then i have to click on "cntrl p" to print, is that right?
Frank .SBuilding EstimatorAsked:
Who is Participating?
 
richardjandrewConnect With a Mentor Commented:
When I open the spreadsheet the references are not as you had described
The formula you need is
=IF(E82="","",G82-(G82*(H82/100)))

Based on your references as originally described it is:
=IF(D82="","",F82-(F82*(G82/100)))
0
 
byundtCommented:
A sample workbook would be helpful for this question.

If the data cells contain a mixture of numbers and arithmetic operators, then we need to parse those cells to extract the numbers.
0
 
byundtCommented:
One possible formula is:
=IF(D82="","",LEFT(D82,LEN(D82)-1)*LEFT(E82,LEN(E82)-1)*G82-LEFT(F82,LEN(F82)-1)*(RIGHT(F82,1) & 1))

This formula assumes:
     D82 is text that contains a number followed by a space and "x"
     E82 is text that contains a number followed by a space and "="
     F82 is a number formatted as percent. So you don't need to divide by 100.
     G82 is text that contains a number followed by "-"
     The desired calculation is 6*2.40*10% - (-1440)
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Frank .SBuilding EstimatorAuthor Commented:
hi byundt, i dont believe such a complex formula would be required. I can achieve the correct qty or result but i need it to be a positibe not a negative qty.

I just need the following formula to = a positive qty, at the moment the following formula =
- 12.96, it must = 12.96

=IF(D82="","",D82*E82*G82/100)-(F82)
0
 
richardjandrewCommented:
Are you just trying to take 10% from the result of D82*E82?
If I understand correctly you shouldn't need to refer to cell F82 at all, in which case you could use the following formula:
=IF(D82="","",(D82*E82)-((D82*E82)*G82))
If you want to use an intermediate formula you could do:
=IF(D82="","",F82-(F82*G82))
This is assuming that the formula in cell F82 is
=D82*E82
0
 
Rob HensonFinance AnalystCommented:
If you are wanting to deduct 10% from the result, the way round it could be to multiply the result by 0.9 ie 90%, so:

=IF(D82="","",((D82*E82)*1-E82)-F82)

Thanks
Rob H
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi experts ive tried your formulas and unfortunately they dont get me the correct result. If you look at the original post, the result should = 12.96 and I dont get that when using your formulas
0
 
byundtCommented:
If 12.96 is the answer, then all the discussion about D82 and E82 is just a red herring. Your formula is one of:
=IF(D82="","",F82*(1-G82))
=IF(D82="","",F82*(1-G82/100))

The reason for two possible formulas is I'm not sure that cell G82 actually contains the value 10%. If you put 0.1 in the cell and format using % format, it will display 10% and the first formula will work. If you put 10 in the cell, then you need to use the second formula.
0
 
Frank .SBuilding EstimatorAuthor Commented:
hi byundt, im still getting the wrong result so i have attached the wksht for your reference.
The cell in question is i82
percentage-ddt-formula-error.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.