Solved

excel 2010 - if statement with % deduction in formula

Posted on 2013-05-09
9
174 Views
Last Modified: 2013-05-23
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?
0
Comment
Question by:FrankSasso
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39154438
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
 
LVL 81

Expert Comment

by:byundt
ID: 39154449
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
 

Author Comment

by:FrankSasso
ID: 39154527
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
 

Expert Comment

by:richardjandrew
ID: 39155004
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

Expert Comment

by:Rob Henson
ID: 39155073
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
 

Author Comment

by:FrankSasso
ID: 39160359
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
 
LVL 81

Expert Comment

by:byundt
ID: 39160378
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
 

Author Comment

by:FrankSasso
ID: 39160402
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
 

Accepted Solution

by:
richardjandrew earned 275 total points
ID: 39160732
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now