Max Sum Condition Formula

dfulm219

New Member
Joined
Mar 23, 2011
Messages
8
I have a range of cells that have numbers in them. When the range reaches a specific number, I need this formula to yield a specific value. When it reaches another maximum, I need it to yield a different specific formula.

For Example:

A1=8, A2=8, A3=8, A4=8, A5=8, A6=8, A7=8, etc.

I need a formula that serves the function of:

When the above cells reach 40, read "A" until it reaches 80 then read "B" until it reaches 120 then read "C" until it reaches 160, then read "D".

Does this make sense?
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi,

If the difference between values are 40, try this in B1

=SUBSTITUTE(ADDRESS(1,INT(SUM(A1:INDEX(A:A,LOOKUP(E1+307,A:A)))/40),2),"$1","")

If you're using Excel 2003 or earlier, this will give you untill IV
If you're using Excel 2007 or later, this will give you untill XFD
 
Upvote 0
Great advice.

Ok, let's use this example:

A2=<Cell Value>, B2=<Cell Value, C2=<Cell Value, D2=<Cell Value> ETC until G2

When the above cells reach <Specific Value>, read "A" until it reaches <Cell Value * 2> then read "B" until it reaches <Cell Value * 3> then read "C" until it reaches <Cell Value * 4>, then read "D" until it reaches <Cell Value * 5> then read "E".

Can you help?
 
Upvote 0
Great advice.

Ok, let's use this example:

A2=<Cell Value>, B2=<Cell Value, C2=<Cell Value, D2=<Cell Value> ETC until G2

When the above cells reach <Specific Value>, read "A" until it reaches <Cell Value * 2> then read "B" until it reaches <Cell Value * 3> then read "C" until it reaches <Cell Value * 4>, then read "D" until it reaches <Cell Value * 5> then read "E".

Can you help?
What is in A2, B2..... until G2? Below is the sample. It will give you If the SUM

01-39 "A"
40-79 "B"
.......
Above 160 will show "E". If this is not you are looking please provide some sample data.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">32</td><td style="text-align: right;;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet7</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A3</th><td style="text-align:left">=LOOKUP(<font color="Blue">INT(<font color="Red">SUM(<font color="Green">A2:G2</font>)/40</font>),{1,2,3,4,5},{"A","B","C","D","E"}</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
Haseeb -

Thanks for your reply.

Here is my example:

D11 through J11 will all be a number. Let's use the number 8 for this example.

D11=8, E11=8 ... J11=8.

I need a formula in D12 that will yield "A" when those numbers (D11:J11) add up to a specific value, we'll use 40 in this example. I need the same formula to yield "B" when the same range of numbers add up to 2x the specific value, 80 in this example. When the same range add up to 3x the specific value, 120, the formula will yield "C" and "D" for 4x the specific number, 160.

To recap, the formula I need will yield the following:

0-40: "A"
41-80: "B"
81-120: "C"
121-160: "D"

This will be based on a specific range of numbers that will repeat infinitely.

In other words, it will read the range - D11:J11 - Over and over again.

I've been racking my head with this for days now. If you could help, I would GREATLY appreciate it!
 
Upvote 0
How about

=CHAR(65+(SUM(D11:J11)/40))

Works with totals from 0 to 1039 inclusive.
 
Upvote 0
Okay, great, that definitely works. I left out a part of the functionality that I need from this formula.

Let me try this again.

mail


So the variables of the page are as follows:

  • Crew Size
  • # of Shifts
  • Shift Length
  • Working Days
  • Remaining Duration of Project
  • Maximum Weekly Hours
All other cells are calculation formulas.

The formula I need would go in cell D12 and continue down to J15.

In each of these cells, I need the formula to yield "A" until the "Maximum Weekly Hours" is met (J3+1), then start yielding "B" until the "Maximum Weekly Hours" is met again (J3*2+1), then yield "C" until it is met again(J3*3+1), then yield "D" (J3*4+1)

The letters represent specific crews. Another ideal function of the formula would be if it could recognize "# of Shifts" In this example, the formula would never go past "B" because there are only two crews plugged in the "# of Shifts" cell. If E2 read 4, it would go up to "D" and so forth.

I hope this makes sense and I hope my image shows up.
 
Upvote 0
While I'm trying to make sense of that, is it possible that the formula could go past "Z"? For example, (J3*27+1)

I have an alternative formula that could cope with that, and way beyond if it's needed.

edit: if I'm reading it right, maybe

=CHAR(65+(SUM(D11:J11)/(J3+1)))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,096
Messages
6,105,935
Members
447,985
Latest member
gigi11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top