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

x
Solved

# If Statement based on multiple cells

Posted on 2012-09-01
Medium Priority
533 Views
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
Android Tablet                                        300                              300
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.

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

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!

A
0

## Featured Post

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…
###### Suggested Courses
Course of the Month20 days, 2 hours left to enroll