# Using different values for a cell

#### Kribulin

##### Board Regular
Okey, I don't think my problem is very complicated, but it's seems to be difficult to explain, so I have tried to make it clearer with the help of a picture.

My setup is the following:

I have a very long list of values in a column (column J). All these values are calculated in accordance with an input value, which is currently given in a single cell (P3).
This is the code of the first cell in the J-column:
Code:
``=WENN(H2+F3>\$P\$3;(H2+F3)-\$P\$3;0)``
Now I would like to make a new column, where the first cell calculates the sum of the original column for an input value "0".
Then the second cell in this column calculates the of the original column for an input value "100".
Then the same with an input value 200, then 300 and so on.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Andrew Poulsom

##### MrExcel MVP
Assuming your list of different input values starts in L3, in M3 enter:

=SUM(IF((H\$2:H\$6+F\$3:F\$7)>L3,(H\$2:H\$6+F\$3:F\$7)-L3,0))

and press Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surround this array formula with curly braces {}.

You will need to adjust the references to columns H and F so that they refer to all the cells in column J (my formula evaluates only 5 rows of data). The formula can be copied down.

#### GlennUK

##### Well-known Member
Have you looked at DATA TABLES? This What-If tool would do exactly what you want.

Although a simpler method might be to have different columns with the various calculations for different input values.

You could also get the sum of that calculation in one go using an array formula, and just do that for different input values. ( ah, Andrew seems to have given you that while I was typing ).

Last edited:

#### Kribulin

##### Board Regular

Andrew, I did as you told me, and the formula in M3 now looks like this:
Code:
``{=SUMME(WENN((H\$2:H\$6+F\$3:F\$7)>L3;(H\$2:H\$6+F\$3:F\$7)-L3;0))}``
and works.

Then when I set my intervals for H and F, like this
Code:
``{=SUMME(WENN((H\$2:H\$17522+F\$3:F\$17522)>L3;(H\$2:H\$17522+F\$3:F\$17522)-L3;0))}``
I get this error message: #NV

I'm not sure if the error is language specific, so here's the reason for this type of error: "a value for a function of formula is not available".

Do you have any idea what the reason could be?

Thank you a lot!

#### Andrew Poulsom

##### MrExcel MVP
The arrays must be the same size (I had rows 2:6 for column H and rows 3:7 for column F). So try eg:

=SUMME(WENN((H\$2:H\$17522+F\$3:F\$17523)>L3;(H\$2:H\$17522+F\$3:F\$17523)-L3;0))

#### Kribulin

##### Board Regular
Okey, thank you!

However, something is still not right. The sum I get for L3=0 is 55,155, when it should be 276697,48 according to my original setup.

The error is due to fact that the H-column is now used and this is also dependent on "the former P3". This as follows:

H3:
Code:
``=P4+F3``
(the first cell is the only exception in the column) (and is consequently the only cell in the column not dependent on P3)

H4:
Code:
``=WENN(H3+F4<0;0;WENN(H3+F4>\$P\$3;\$P\$3;H3+F4))``
H5:
Code:
``=WENN(H4+F5<0;0;WENN(H4+F5>\$P\$3;\$P\$3;H4+F5))``
So if the H-column is to be used, this has to be taken into account as well. I'm not sure how though?

Thank you for your help!

PS. WENN = IF

Last edited:

#### Andrew Poulsom

##### MrExcel MVP
It would have been helpful to know that column H was also dependent on P3 at the outset. Can you post up a small but representative sample of your data with formulas?

#### Kribulin

##### Board Regular
I'm sorry, from the beginning I did not realize that the information was necessary; my apologies.

The sample:

<script language="JavaScript" src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></script><center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="17" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - Model_extratemp.xls</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: 11.0 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="17" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td align="right" valign="center"><form name="formCb605117"><input onclick='window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);' value="Copy Formula" name="btCb942116" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="17" bgcolor="white"><table border="0"><tbody><tr><form name="formFb202339"></form><td style="width: 60px;" align="center" bgcolor="white"><select onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name="sltNb447362"><option value="=Neusiedl!A18">A3</option><option value="=Neusiedl!H18">B3</option><option value="=Load_WD_year!B3">C3</option><option value="=B3-C3">E3</option><option value="=E3*0,5">F3</option><option value="=P4+F3">H3</option><option value="=WENN(H2+F3<0;-(H2+F3);0)">I3</option><option value="=WENN(H2+F3>\$P\$3;(H2+F3)-\$P\$3;0)">J3</option><option value="=Neusiedl!A19">A4</option><option value="=Neusiedl!H19">B4</option><option value="=Load_WD_year!B4">C4</option><option value="=B4-C4">E4</option><option value="=E4*0,5">F4</option><option value="=WENN(H3+F4<0;0;WENN(H3+F4>\$P\$3;\$P\$3;H3+F4))">H4</option><option value="=WENN(H3+F4<0;-(H3+F4);0)">I4</option><option value="=WENN(H3+F4>\$P\$3;(H3+F4)-\$P\$3;0)">J4</option><option value="=Neusiedl!A20">A5</option><option value="=Neusiedl!H20">B5</option><option value="=Load_WD_year!B5">C5</option><option value="=B5-C5">E5</option><option value="=E5*0,5">F5</option><option value="=WENN(H4+F5<0;0;WENN(H4+F5>\$P\$3;\$P\$3;H4+F5))">H5</option><option value="=WENN(H4+F5<0;-(H4+F5);0)">I5</option><option value="=WENN(H4+F5>\$P\$3;(H4+F5)-\$P\$3;0)" selected="selected">J5</option><option value="=Neusiedl!A21">A6</option><option value="=Neusiedl!H21">B6</option><option value="=Load_WD_year!B6">C6</option><option value="=B6-C6">E6</option><option value="=E6*0,5">F6</option><option value="=WENN(H5+F6<0;0;WENN(H5+F6>\$P\$3;\$P\$3;H5+F6))">H6</option><option value="=WENN(H5+F6<0;-(H5+F6);0)">I6</option><option value="=WENN(H5+F6>\$P\$3;(H5+F6)-\$P\$3;0)">J6</option><option value="=Neusiedl!A22">A7</option><option value="=Neusiedl!H22">B7</option><option value="=Load_WD_year!B7">C7</option><option value="=B7-C7">E7</option><option value="=E7*0,5">F7</option><option value="=WENN(H6+F7<0;0;WENN(H6+F7>\$P\$3;\$P\$3;H6+F7))">H7</option><option value="=WENN(H6+F7<0;-(H6+F7);0)">I7</option><option value="=WENN(H6+F7>\$P\$3;(H6+F7)-\$P\$3;0)">J7</option><option value="=Neusiedl!A23">A8</option><option value="=Neusiedl!H23">B8</option><option value="=Load_WD_year!B8">C8</option><option value="=B8-C8">E8</option><option value="=E8*0,5">F8</option><option value="=WENN(H7+F8<0;0;WENN(H7+F8>\$P\$3;\$P\$3;H7+F8))">H8</option><option value="=WENN(H7+F8<0;-(H7+F8);0)">I8</option><option value="=WENN(H7+F8>\$P\$3;(H7+F8)-\$P\$3;0)">J8</option><option value="=Neusiedl!A24">A9</option><option value="=Neusiedl!H24">B9</option><option value="=Load_WD_year!B9">C9</option><option value="=B9-C9">E9</option><option value="=E9*0,5">F9</option><option value="=WENN(H8+F9<0;0;WENN(H8+F9>\$P\$3;\$P\$3;H8+F9))">H9</option><option value="=WENN(H8+F9<0;-(H8+F9);0)">I9</option><option value="=WENN(H8+F9>\$P\$3;(H8+F9)-\$P\$3;0)">J9</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="white"><input size="80" value="=WENN(H4+F5>\$P\$3;(H4+F5)-\$P\$3;0)" name="txbFb150492"></td></tr></tbody></table></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="center" width="2%">
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>

#### Andrew Poulsom

##### MrExcel MVP
In G3 enter:

=SUM(F\$2:F2)

and copy down. In M3 enter:

=SUM(IF((IF(G\$3:G\$9>L3,L3,G\$3:G\$9)+F\$3:F\$9)>L3,(IF(G\$3:G\$9>L3,L3,G\$3:G\$9)+F\$3:F\$9)-L3,0))

press Ctrl+Shift+Enter and copy down.

That doesn't cater for the different formula in F3, but maybe you can adjust for that.

Replies
1
Views
192
Replies
5
Views
206
Replies
5
Views
177
Replies
6
Views
230
Replies
3
Views
321

1,191,057
Messages
5,984,408
Members
439,884
Latest member
BrownEyedGirl

### 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?

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