If <$ then %, if >$$ then higher % formula?

[LKM Kevin]

Hi,

I have a done a little searching and could not figure this one out.

I have a total cell that needs to have a payment breakdown figured below it.

If C18 is the cell total and rows 20:25 show the payment breakdown, this is what I am trying to get to work in formula:

IF C18 <$5000 display 50% in C21 and 50% in the C22, and do not show anything in row 23 ("").
IF C18 $5001-$30000 display 20% in C21, 30% in C22, 50% in C23
IF C18 >$30001+ display 20% in C21, 30% in C22, 30% in C23, 20% in C24

Also, I would like to have the words Deposit, Start, Mid Pay, Finish, show up in respect to the percentages that are to be paid and hide the ones not being used. If it was <$5000 then only deposit and finish would be displayed, $5k-$30k would show Deposit, Start, Finish, etc.

How do I have the multiple ranges with if then statements to change based on what the initial value is?

Thanks!

[ben_hensel]

Classic use of approximate MATCH in a lookup function.

C21 =INDEX({0.5,0.2,0.2}, MATCH($C$18, {0,5000,30000}, 1))
C22 =INDEX({0.5,0.3,0.3}, MATCH($C$18, {0,5000,30000}, 1))
C23 =INDEX({"",0.5,0.3}, MATCH($C$18, {0,5000,30000}, 1))
C24 =INDEX({"","",0.2}, MATCH($C$18, {0,5000,30000}, 1))

(Then format cells as % instead of pulling raw numbers).
You could nest the INDEX and MATCH {arrays} in a separate lookup table if you wanted to get fancy with breakdown of percentages vs. payments or whatever.

Then for the text descriptors, I'd just run it off CHOOSE like so:

B21 ="Deposit"
B22 =CHOOSE(COUNT($C$20:$C$23), "Error", "Finish", "Start", "Start")
B23 =CHOOSE(COUNT($C$21:$C$24), "", "", "Finish", "Mid-Pay")
B24 =CHOOSE(COUNT($C$21:$C$24), "", "", "", "Finish")

[FDibbins]

Try this...
C21=IF($C$18 >30001,20%,if($C$18>5000,20%,50%))
C22=IF($C$18 >30001,30%,if($C$18>5000,30%,50%))
C23=IF($C$18 >30001,30%,if($C$18>5000,50%,""))
C24=IF($C$18 >30001,20%,if($C$18>5000,"",50%))

You may need to work with the tow "" options if you need to apply % to values elsewhere

[LKM Kevin]

I may have described that wrong, B21, B22, B23, B24 need to display the 20%, 30%, 30%, 20%, 50%, etc. So I will put those formulas in the B column.

Column C needs to display the value of that percentage, so if the total was $1000 then 20% would be $200 that would be displayed.

Thanks for the replies!

[ben_hensel]

I may have described that wrong....

Nah, what happened was since you didn't bother to post an attachment for me to work with I worked in a fresh sheet, and was offset a column to the right compared to your example.

Just shift it over one.

[FDibbins]

O that part is fairly simple...
=if(B21="","",$C$18*B21)

Thanks for the feedback, too