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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
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
 

dfulm219

New Member
Joined
Mar 23, 2011
Messages
8
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?
 

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
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 />
 

dfulm219

New Member
Joined
Mar 23, 2011
Messages
8

ADVERTISEMENT

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!
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

=LOOKUP(SUM(D11:J11),{0,41,81,121,161},{"A","B","C","D",""})
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,980
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about

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

Works with totals from 0 to 1039 inclusive.
 

dfulm219

New Member
Joined
Mar 23, 2011
Messages
8
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.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Perhaps

=LOOKUP(SUM(D11:J11),J3*{0,1,2,3}+1,{"A","B","C","D"})
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,980
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,923
Members
414,416
Latest member
Nobu

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
Top