If Statement based on multiple cells

Hi Experts!

I am in need of some help with a challenge involving an if statement.

Column A                                              Column B                   Column C

E-Edition-Digital Verified                      100                               0
IPAD                                                         200                              200
Android Tablet                                        300                              300
e-Reader                                                  400                               400
Iphone Paid                                             500                              150

Total                                                         1500                            1050

What we need to do is take 70% of the total of column B or 1050 which leaves 30% or 450.  
We need to then take from column A--Row 1 and subtract as much of the 450 but not lower than zero.  In this case it leaves 0 in Column C--row 1.  We now need to take the remaining amount of 350 and take it off of Column c -- Row 5.  This now leaves column c with the 70% from column b by taking it off two different rows.  Sometimes row 1 will be large enough that we won't have to take anything off row 5.  We just can't ever take row 1 lower than 0.  

I hope that makes some sense.  I really appreciate any help with this issue.

Thanks in advance.

Who is Participating?
thehagmanConnect With a Mentor Commented:
C1 = max(0, B1 -0.3*B6)
C5 = max(0, B1+B5-C1-0.3*B6)

What if decreasing C1 and C5 both to 0 still does not decrease the total by the desired 30%?
spudmccAuthor Commented:
Thank you so much for your resolution.  It works perfect.  We won't ever have a situation where both will get to zero or below.

I really appreciate your time and knowledge.  

Thanks again!

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.