Max Sum Condition Formula

[dfulm219]

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?

 

[Haseeb Avarakkan]

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]

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]

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]

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]

Try

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

 

[jasonb75]

How about

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

Works with totals from 0 to 1039 inclusive.

 

[dfulm219]

Okay, great, that definitely works. I left out a part of the functionality that I need from this formula.

Let me try this again.

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]

Perhaps

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

 

[jasonb75]

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)))