[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


If Statement based on multiple cells

Posted on 2012-09-01
Medium Priority
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

thehagman earned 2000 total points
ID: 38357463
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

ID: 38357996
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

872 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