If Statement based on multiple cells

Posted on 2012-09-01
Last Modified: 2012-09-01
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.

Question by:spudmcc
    LVL 20

    Accepted Solution

    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%?

    Author Closing Comment

    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!


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    729 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

    19 Experts available now in Live!

    Get 1:1 Help Now