PDA

View Full Version : [SOLVED] Improve Nested IF Formula



pegbol
02-25-2005, 05:50 PM
Hello Jake,

I would like to request one more time your kind assistance.

I have the next formula in column E (%) of my Sheet1:

=IF(OR(A4="",B4="",D4=0), 0, IF(A4="ER",D4/(SUMIF($B$4:INDEX(B:B,MATCH(9.99999999999999E+307,D:D)), "ING", $D$4:INDEX(D:D,MATCH(9.99999999999999E+307,D:D)))), IF(OR(OFFSET(D$4,(COUNTA(D$4:D$65536)-1),0)>0.99, OFFSET(D$4,(COUNTA(D$4:D$65536)-1),0)<-0.99), D4/(OFFSET(D$4,(COUNTA(D$4:D$65536)-1),0)),0)))*100

This formula works almost OK.
But, I do need the formula makes what I explain in my enclosed file.

I try and try but I cannot figure it out a solution.


So, I would highly appreciate if you help me to correct the formula in order that do what I need.

kindest regards,
Pedro.

Anne Troy
02-25-2005, 06:10 PM
Improve a Formula. Please!!! DRJ.

Special requests cost extra, Pedro!
:rofl

Anne Troy
02-25-2005, 06:12 PM
I edited Pedro's post to get rid of the smilies in his formula.

Jake: He had all the ones that beg for help on there, so you'd better help him.

pegbol
02-25-2005, 07:18 PM
Thanks so much Dreamboat!!!

Jacob Hilderbrand
02-25-2005, 07:24 PM
Pedro

Can you just explain what you would like to calculate and I will made a VBA UDF for you.

pegbol
02-26-2005, 08:21 AM
.
Thanks Jake for your reply.

It is kind complicated to explain in English (for me) cause it is not my native language and do not speak and write the language as I'd want to. (But, I am learning).
I wil try to describe my request the best I can.

So, here it is:

I have 3 columns "CLAS", "TYPE" and "2003". Based on the data of these 3 columns I need to calculate in column "%", the vertical variation of 2003 (in relation of the Subtotals). I say Subtotals cause if I use an autofilter the sum of "2003" will vary.

Example:
If cell D4=2801 and cell D22=363 , the result in cell E4 would be 771 , (2801/363*100).
But, the result of E4 will depend that if A4 is BG or ER.

If A4=ER, cell E4 is the result of division between cell D4 and the sum of all ING of column "TYPE" , (D4/SUMIF(B:B,"ING",D:.D))

If A4=BG, cell E4 should be 0.
Now, here starts my problem. I will use an Autofilter. What I need is when I filter in column "TYPE" (the criteria would be ACT, PAS or PAT) the result of column "%" should show the actual division between D4 and subtotals of "2003". This is, if criteria is ACT, cell E4 is (2801/9026*100), and the same for the next cells that are filtered with ACT.
Same with PAS and PAT.

If I only filter column "CLAS" and the criteria is BG all the numbers in column "%" should be 0 (cells that are filtered with BG).
Also, I made a condition in the formula: ".....IF(OR(OFFSET(D$4,(COUNTA(D$4$d65536)-1),0)>0.99, OFFSET(D$4,(COUNTA(D$4.$d65536)-1),0)<-0.99), .....". This is if the subtotals of "2003" are in the ranges of -0.99 and 0.99 the numbers should be 0.

The columns "CLAS", "TYPE" and "2003" have dynamic ranges

The formula I put in my first message and that is in my file, do almost everything OK except that I cannot get that show the zeros '0' in column "%" for all BG in column "CLAS", when I do not use the Autofilter.

I apologize if my explanation was not clear enough, and for my grammatical errors.

Appreciate your kind attention.

regards,
Pedro.


PS. I will input this formula in VBA code.
.
.

patrickab
02-26-2005, 02:10 PM
=IF(A4="ER",D4/SUMIF(B:B,"ING",D:D), IF(A4="BG", 0, IF(A4="PAS",D4/SUMIF(B:B,"PAS",D:D), IF(A4="PAT",D4/SUMIF(B:B,"PAT",D:D),""))))

I think this does what you want without using dynamic ranges. Worth a try if nothing else!

pegbol
02-26-2005, 03:15 PM
.
patrickab,

I appreciate your contribution.

Your formula show me 0's for all the records even I use the autofilter.

I have to say that your formula made me improve the mine one.

My new and improved formula is:


=IF(OR(A4="",B4="",D4=0), 0, IF(A4="ER",D4/(SUMIF(B:B,"ING",D:.D)), IF(OR(OFFSET(D$4,(COUNTA(D$4d$65536)-1),0)>0.99, OFFSET(D$4,(COUNTA(D$4d$65536)-1),0)<-0.99), D4/(OFFSET(D$4,(COUNTA(D$4d$65536)-1),0)),0)))*100

But, still has the same problem. I cannot get to show the zeros for all BGs


regards,
Pedro.
.
PS. Maybe my formula seems to be wrong This is for the smilies that show when I write letter "D".
.

.
I am still trying to get a solution. I made a new improvement (I think):


=IF(OR(A4="",B4="",D4=0), 0, IF(A4="ER",D4/(SUMIF(B:B,"ING",D:.D)), IF(OR(LOOKUP(9.99999999999999E+307,D:.D)>0.99, LOOKUP(9.99999999999999E+307,D:.D)<-0.99), D4/(LOOKUP(9.99999999999999E+307,D:.D)),0)))*100

And yes, it still has the same problem.

A question:
How can I round to zero if my number is between the ranges of -0.99 to 0.99? What function would I need to do this?.

Thanks in advance for your assistance.

regards,
Pedro.
.

Jacob Hilderbrand
02-26-2005, 05:03 PM
So all you really need is for it to show 0 for all BGs when you are not filtering correct?

If so, then we can get a little creative and use some VBA.

Use this formula for E4 and fill down.


=IF(AND(ISERROR($K$1),A4="BG"),0,IF(OR(A4="",B4="",D4=0), 0, IF(A4="ER",D4/(SUMIF(B:B,"ING",D:D)), IF(OR(OFFSET(D$4,(COUNTA(D$4:D$65536)-1),0)>0.99, OFFSET(D$4,(COUNTA(D$4:D$65536)-1),0)<-0.99), D4/(OFFSET(D$4,(COUNTA(D$4:D$65536)-1),0)),0)))*100)

In K1 use this formula. You can change the cell from K1 to another cell, just remember to modify the above formula as well.


=RIGHT(FilterCriteria(A:A), LEN(FilterCriteria(A:A)) - FIND("=",FilterCriteria(A:A)))

In the VBE Insert a Module and add this code.


Option Explicit

Function FilterCriteria(Rng As Range) As String
Dim Filter As String
Filter = "No Filter"
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then
GoTo Finish
End If
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then
GoTo Finish
End If
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function


If Smilies are messing you up (you want to put :D and not have that become a Smilie) go to "Go Advaced" when you reply. And scroll down to the checkbox that reads "Disable smilies in text" and check it.

pegbol
02-26-2005, 07:17 PM
Hello Jake,

Really thanks so much for your valuable help.

I just got to the Forum and read your amazing solution.


So all you really need is for it to show 0 for all BGs when you are not filtering correct?

Yes!!! that is it. You said it in few words much much better than my long explanation.


I was working on my side trying to get a solution. And I also would like to say that I finally did it too.

Now, I am on a dilemma. Your solution is really great!!!!!. Wich option you recommend me to use?. Maybe my option could present errors with other data?.


The next formula works as I wanted to.


=IF(OR(A4="",B4="",D4=0),0, IF(A4="ER",D4/(SUMIF(B:B,"ING",D:D)), IF(LOOKUP(9.99999999999999E+307,D:D)=0,0, IF(OR(LOOKUP(9.99999999999999E+307,D:D)= SUMIF(B:B,"ACT",D:D), LOOKUP(9.99999999999999E+307,D:D)= SUMIF(B:B,"PAS",D:D), LOOKUP(9.99999999999999E+307,D:D)= SUMIF(B:B,"PAT",D:D), LOOKUP(9.99999999999999E+307,D:D)= (SUMIF(B:B,"PAS",D:D)+SUMIF(B:B,"PAT",D:D))), D4/LOOKUP(9.99999999999999E+307,D:D),0))))*100

The only problem is: that it's too long. So, I would appreciate help me to make it shorter and improve it. Because, I will input this formula via VBA code.

I also modified the formula of my subtotals of column "2003" to the next:

=IF(OR(SUBTOTAL(9,D4:D21)>0.99,SUBTOTAL(9,D4:D21)<-0.99),SUBTOTAL(9,D4:D21),0)

I enclose my file with this new formulas.

Jake. Thanks so much for your kind assistance.

kindest and best regards,
Pedro.
.

Jacob Hilderbrand
02-26-2005, 07:38 PM
To shorten it a bit you could put 9.99999999999999E+307 into a cell somewhere then just refer to that cell in your formula. You could also do the same thing with the SumIfs.

pegbol
02-27-2005, 07:42 AM
.
Jake,

I have no words to express my complete gratitude for your kind help. :bow:
Your solution is working beautifully!!!!!.:clap:

Your assistance is really important to me.

One more time, Thanks so much Jake.:hi:

best regards,
Pedro.
La Paz, BOLIVIA.

PS. I also appreciate the kind message of Anne to my PM. Thank you so much Anne.
:beerchug:
.

Jacob Hilderbrand
02-27-2005, 07:47 AM
You're Welcome :beerchug:

Keep the questions coming. :)