In Between IF AND Excel Formula HELP

[avargas232]

I'm not really too sure how to word this, and I think I'm burnt out from looking at it...so I may be overthinking it. I currently have a formula that is sufficient enough, but I want to take it another level and I just can't figure it out.

My current formula is IF(I6="","",IF(AND(I6>=D6,I6<E6),B6*D$4,IF(AND(I6>=E6,I6<F6),B6*E$4,IF(AND(I6>=F6,I6<G6),B6*F$4,IF(I6>=G6,B6*G$4,IF(I6<D6,0,""))))))

Here is an example of the file:

https://www.dropbox.com/s/rjrymog7f636jza/Percent%20Calc.xlsx?dl=0

and also in cell J12 is the output I'd like.

Hope someone can help...thanks!!!

 

[Gerald Higgins]

Hi.

I don't wish to follow your link.

It looks like some of your formula has been cut off, perhaps because of < or > characters, which are a known issue on this board. Try putting a space character after each of those characters.

Also, please can you explain exactly what you want this to do ?

 

[avargas232]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Excel Line 4[/TD]
[TD]Weight[/TD]
[TD]25%[/TD]
[TD]50%[/TD]
[TD]100%[/TD]
[TD]150%[/TD]
[TD]YTD Performance[/TD]
[TD]Performance Output[/TD]
[/TR]
[TR]
[TD]Current Formula[/TD]
[TD]Line 10[/TD]
[TD]15%[/TD]
[TD]11.4%[/TD]
[TD]12.8%[/TD]
[TD]14.2%[/TD]
[TD]17.0%[/TD]
[TD]12.1%[/TD]
[TD]3.75%[/TD]
[/TR]
[TR]
[TD]Output Needed[/TD]
[TD]Line 11[/TD]
[TD]15%[/TD]
[TD]11.4%[/TD]
[TD]12.8%[/TD]
[TD]14.2%[/TD]
[TD]17.0%[/TD]
[TD]12.1%[/TD]
[TD]5.63%[/TD]
[/TR]
</tbody>[/TABLE]


My Current Formula that gives me the Output 3.75% is
<e10),c10*d$4,if(and(h10><f10),c10*e$4,if(and(h10><g10),c10*f$4,if(h10><d10,0,"")))))). what="" it's="" basically="" doing="" is="" since="" my="" ytd="" performance="" lands="" inbetween="" the="" 25%="" and="" 50%,="" it="" takes="" 15%*25%="3.75%." but="" i'd="" like="" a="" formula="" that="" will="" take="" step="" up="" give="" me="" more="" precise="" answer="" in="" second="" line.="" 12.1%="" between="" both="" thresholds="" for="" to="" 15%*37.5%="5.63%." i="" want="" multiply="" by="" number="" (37.5%)="" versus="" minimum="" threshold="" (25%).
<e10),c10*d$4,if(and(h10><f10),c10*e$4,if(and(h10><g10),c10*f$4,if(h10><d10,0,""))))))
<e10),c10*d$4,
<f10),c10*e$4,
<g10),c10*f$4,
<d10,0,""))))))<d10,0,""))))))
<e10),c10*d$4,if(and(h10><f10),c10*e$4,if(and(h10><g10),c10*f$4,if(h10><d10,0,""))))))
IF(H10="","",IF(AND(H10>=D10,H10<E10),C10*D$4,IF(AND(H10>=E10,H10<F10),C10*E$4,IF(AND(H10>=F10,H10<G10),C10*F$4,IF(H10>=G10,C10*G$4,IF(H10<D10,0,""))))))

I entered the excel lines just so you can know where I had it in excel...the columns all start from A. Hope this helps...thanks!</d10,0,""))))))
</g10),c10*f$4,if(h10></f10),c10*e$4,if(and(h10></e10),c10*d$4,if(and(h10></d10,0,""))))))<d10,0,""))))))
</g10),c10*f$4,
</f10),c10*e$4,
</e10),c10*d$4,
</d10,0,""))))))
</g10),c10*f$4,if(h10></f10),c10*e$4,if(and(h10></e10),c10*d$4,if(and(h10></d10,0,"")))))).></g10),c10*f$4,if(h10></f10),c10*e$4,if(and(h10></e10),c10*d$4,if(and(h10>

 

[avargas232]

My Formula is not pasting correctly so I'm not sure. I'm just going to try and explain it.

Currently Since the 12.1% is in between 11.4% and 12.8% it's taking the weight 15%* threshold 25% and giving me 3.75%. This is correct and I have a long =IF and formula, but like i said it keeps cutting it off.

I want to take a step further and instead of the original output I want the second output of 5.63%. It's basically taking the precise threshold percent of 37.5%* and multiplying it by the goal 15% to give me the 5.63%. I just can't figure out a formula to get to that.

Right now to make things simple i have a formula =if (h10="","",IF (And (H10>=D10,
<e10) c10*="" d4="" ....so="" on="" and="" so="" forth.
and H10 is less than E10, then give me c10*d4.

Sorry the formula is weird, but it just keeps cutting it off. Thanks again!<e10), c10*d4....so="" on="" and="" so="" forth.<="" html=""></e10),></e10)>

 

[dreid1011]

IF(I6="","",IF(AND(I6>=D6,I6<e6),b6*d$4,if(and(i6>=E6,I6<f6),b6*e$4,if(and(i6>=F6,I6<g6),b6*f$4,if(i6>=G6,B6*G$4,IF(I6<d6,0,""))))))

 

[avargas232]

Yes, that is the formula that I have in the Current Formula line item. But I'd like to take it a step further and instead of b6*d$4 (.15*.25), I'd like to incorporate the actual % in between that it's calculating. For Example the 12.1% is right in the middle of the 25% and 50%...which the formula would be b6*.375 which gives me the 5.63%. Hope that clears up my dilemma.

Thanks for your help!

 

[dreid1011]

Based on your posted sample and the formula, I can't tell what information is in what cells. It's not quite lining up for me and that may be why I can't seem to figure out what you're trying to accomplish. Can you break it down step by step and how you are coming up with the performance output.

 

[avargas232]

Every time I post the formula it goes crazy and cuts it off or posts all that other stuff that is irrelevant. Ok let me see if i can break it down a bit better.

In the Current Formula line, I have a formula that is exactly like you had posted. The Current YTD Performance is 12.1% is between the 25% threshold but less than the 50% mark, so I'm taking the 1st tier column of 25% and multiplying it by my Goal Weight for that line. (.15*.25=.0375) So my current goal weight, which is my "Performance Output", is 3.75% of the 15% goal.

In the Output Needed line, which is what I'd like to do. Same criteria as above, but instead of taking the 25% threshold, I want to get the actual % of 37.5...I get that by saying 12.1% is right in between each threshold of 25% and 50% of goal. That would mean that I would multiply my Goal Weight for that line by 37.5% instead of 25%. (.15*.375=.0563) So my actual Performance Output would be 5.63% of the 15% goal.

I Hope that makes a bit more sense...i Wish i could just upload the file. Thanks for taking a ponder at it!

 

[dreid1011]

Ok, I have a much better understanding now. It wasn't clicking before. I will have a go and see what I can do.

 

[dreid1011]

Ok, I found a potential solution. Depending on how you feel about using some space on the sheet out of sight, only because I couldn't make the formulas work the way I wanted them to in a single cell.
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD][/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]0.114
[/TD]
[TD]0.170
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]0.128
[/TD]
[TD]0.142
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]0.142
[/TD]
[TD]0.128
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]0.170
[/TD]
[TD]0.114
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]0.5
[/TD]
[TD]0.114
[/TD]
[TD]0.128
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD]0.25
[/TD]
[TD]0.5
[/TD]
[/TR]
</tbody>[/TABLE]


Range V1:V4 - the percentages from 11.4% - 17% in ascending order
Range W1:W4 - The percentages from 11.4% - 17% in descending order

G5 is YTD Performance
C4:F4 is percentages 25% - 150% in original table
C5:F5 is percentages 11.4% - 17% in original table

Range U6 - formula =(G5-V6)/(W6-V6)
Range V6 - formula =INDEX(V1:V4,MATCH(G5,V1:V4,1))
Range W6 - formula =INDEX(W1:W4,MATCH(G5,W1:W4,-1))
Range V7 - formula =LOOKUP(V6,C5:F5,C4:F4)
Range W7 - formula =LOOKUP(W6,C5:F5,C4:F4)

In your original table, Performance Outlook column:
=B6*PERCENTILE(V7:W7,U6)

B6 is Weight column on your Output Needed row.

I couldn't get Excel to accept the Percentile function with the Index/Match. Otherwise, this could have been a one-cell wonder.