Reference to cell with numbers returns other numbers than in the original cell

[liekebreure]

Hi all,

I'm trying to make a reference to a set of cells by using the following formula:

=VLOOKUP(E5;'Part 2'!$B$11:$O$22;14)

As you might see, the cells i'm trying to refer to are on a different worksheet.

The original values are:
19
0
0
18
0
0
0
8
0
0
0
5

The values I receive from the formula are:
19
0
0
18
0
0
12
0
0
13
0
0

As you see, those differ. I don't understand why. I also tried to refer to those cells using index(...;Match), but this also does not work.

Can you please help me?

[Glenn Kennedy]

If you are looking for an exact match, you need:

=VLOOKUP(E5;'Part 2'!$B$11:$O$22;14;FALSE)

If that's not it... read the yellow banner (top) about sample files... and post one.

[liekebreure]

Hi,

That did not yet work. I just added the workbook.The green worksheets are to solve this problem. The data in the black worksheet is not needed for this problem, but those values are referred to in the green worksheets.

The question is about the values marked in red.

I hope you can help me!

Regards

[liekebreure]

Does somebody else maybe know what can be the root cause for this?

[Glenn Kennedy]

There seem to be major problems with your sheet. I suspect that they are all down to the circular references on both the green sheets. Some of the formulae (in your version of Excel probably just don't work:

=$C$32-IFERROR(VLOOKUP(@$E$11:$E$22,$B$32:$C$42,2),"0")+75

seems to be looking up 12 differnet values. In Excel 2010... it can't.

the formula in oderdeel 2, I11 is so complicated looking and refers to all sorts of different rows, it's impossible to decipher.

All I know is that if you copy PASTE VALUES in onderdeel 2 column O, the formula works... but as a forumla it does not. There is something fundamentally wrong with your sheet. Fix the circular references. Only you know what the sheet is supposed to be doiing. Only you can fix it.

[swaatacba]

This is strange and, sadly, I do not have a definitive soluntion. However, I have mapped the worksheets of your original workbook to a separate file.

This shows that the relevant formulae (F10) on your worksheet called 'Cashflow restrictie' calls values from a table in 'Onderdeel 2', column 14. This particular column has four empty cells.

I attach a copy of the MapBook, so you can see for yourself.

[liekebreure]

I think that the problem is about the circular reference. I will tell you why I need this circular reference.

Can somebody help me how I can do the following in Excel, without using circular references?

I need to write a formula that is about volume discount.
So, if you buy
1 unit - $300 per unit
5 units - $250 per unit
10 units- $220 per unit

I want Excel to look up which price corresponds with a certain amount of units, based on the forecast. Say that the forecast for the following months is; 3 units,4 units,3 units.
Then, in the first period I want Excel to tell me the corresponding price for the first two periodes(3+4=7 units, which is the amount of 5 units; 250). I managed to do that by using the following formula: VLOOKUP([Sum of requirements];Table10;2). This works like I want.

Then, there is a related formula that determines, based on the order costs and storage costs and the amount of volume discount, if it is beneficial to order the two periods together. Say that, based on a.o. the volume discount, it is beneficial to order the first two periods together; 7 units for $250 euros per unit.

Then I want to check if it is also beneficial to order the amount of the third period (3 units) together with those 7, so you get the volume discount of 10. I only want to calculate this if the previous period (4 units) also needs to be ordered together with the first period. But when I add this if-condition to the formula, it says it is a circular reference, which is not allowed. The circular reference is the case since the volume discount that needs to be looked at for the third period, relates to the decision about combining the first two periods, which is based on the volume discount there.

How can I solve this? Thanks in advance!

[Glenn Kennedy]

I don't follow all of that.. but does this help at all. Change the values in A2 & check the result in B2.

[liekebreure]

Thanks Glenn!

I think your formula works.

There is formula determining when you need to order the materials for multiple periods together:
If 2 * (storage costs + missed volume discount per unit) / order costs > Requirement for period _t+n * n * (n+1)

I inserted that to the file you just created, with an example.

If this formula is true, then it will be beneficial to order the requirements for the next period together with your requirements now. Then, I want to check if it is also beneficial to order for the third period already now. But then, the unit price may change again, because you have a different volume discount threshold, namely for the three periods. Then Excel tells that it is a circular reference.

I don't know if I explained it more clearly now. Thanks anyways for checking!

[Glenn Kennedy]

I still don't quite understand (no coffee yet). Can you populate the sheet with manually calculated answers, in the place you expect to see them?

[liekebreure]

Hi Glenn,

Does column F help you in understanding?

[Glenn Kennedy]

Yes, but... Please add manually calculated answers, as already requested.

[liekebreure]

I added the formulas with manual calculations. Can you help me writing the formulas of column C and D, without circular references?

[liekebreure]

Sorry, I see that I uploaded the wrong file. I changed the value of cell C4

[Glenn Kennedy]

Sorry. I have no idea what's going on. I still do not know what you expect to see in any cell, anywhere, as your expected answer. Over to others.

[liekebreure]

Can somebody else help me?

Or tell me how I can tell that I want to sum the cells until the last row where it is stated a certain text? So say this is the sheet
Row Value Condition SUM
1 5 Yes
2 66 No
3 2 No
4 4 Yes
5 5 No
6 45 Yes

Then I want to sum the numbers until the previous 'Yes'.
So in row 1 I want in the SUM column the value 5.
In row 2 I want the value 5+66=71
In row 3 I want the value 5+66+2=73
In row 4 I want the value 4

How would this formula look like?

[swaatacba]

If you had a single maximum sum value, this could work. But you haven't told us that.

Taking your logic on from Row 4

In Row 4 we have 4
In Row 5 we have 4 + 5 = 9
But in Row 6 we start again at 45.

There is no obvious arithmetic relationship here and we cannot determine a maximum value?

If you want us to model a sequence of values then you must explain the logic behind the sequence.

[liekebreure]

Hi Swaatacba,

Thanks for your response! True, there is no relationship between the numerical values of the rows. Though, I want to make the difference based on the 'condition' column. So if in the 'condition' column, it says yes, a 'new' sumrange starts.

So, since row 1, row 4 and row 6 have the condition YES; I want to sum the columns with condition 'NO' until the previous 'YES'condition.

Another example
Row Numerical_value Condition SUM (To be filled in by the formula asked for)
1 45 YES 45
2 4 NO 49
3 32 YES 32
4 8 NO 40
5 6 NO 46
6 10 YES 10

[Glenn Kennedy]

Your MUCH simpler presentation helps. There are two mistakes. D4 should be 81, not 92...

also please explain which is correct at tthe blue shaded cells.

[liekebreure]

Hi Glenn,

It should reset, sorry you're right. D4 should therefore be 32.

[Glenn Kennedy]

Try:

=SUM(INDEX(B:B,AGGREGATE(14,6,ROW(C$2:C2)/(C$2:C2="Yes"),1)):B2)

[liekebreure]

What might also be good to notice, is that in the 'real case' the conditions of YES and NO are also determined by this SUM function of the next row. So that's why Excel shows the announcement that it is a circular reference.

[swaatacba]

Sorry, this was out of time!

[swaatacba]

the conditions of YES and NO are also determined by this SUM function of the next row.

Should you not delay the calculation until the next row is available. That way you would only calculate once.

[liekebreure]

Thanks! That aggregrate formula works!

Do you also know what formula I can use to sum the values in column B untill the next 'NO' in column C?
So if the condition is YES; sum up that value of the row with YES + all following values with condition NO (untill the next 'YES).

[Glenn Kennedy]

To avoid further confusion... add expected results to my last file & repost.

[liekebreure]

Please see attachment

[Glenn Kennedy]

Try this... I'm not entirely confident, as the sample was a bit small...

=IF(B2="","",IF(C2="No",0,SUM(B2:INDEX(B:B,AGGREGATE(15,6,ROW($C3:$C8)/(($C3:$C8="Yes")+($C3:$C8="")),1)-1))))

[liekebreure]

Thanks! It seems to work for the small sample. But for some reason it does not work for the sample I'm actually trying to solve. It consists of 12 rows. Please see attachment. If FALSE, I want to sum up all the rows that are true, until the next 'FALSE'. Just like in the upper example.
I also added the expected solution.

Thanks in advance for checking

[Glenn Kennedy]

Changing Yes and No to TRUE and FALSE seems to have annoyed AGGREGATE and it stopped working. V odd. Anyway, it is behaving itself, using a slightly different strategy!

=IF(A13="","",IF(B13=TRUE,0,SUM(A13:INDEX(A:A,AGGREGATE(15,6,ROW($B14:$B25)/((ISNUMBER(SEARCH("False",$B14:$B25)))+($B14:$B25="")),1)-1))))

[liekebreure]

Thank you so much!! This is amazing

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[liekebreure]

Hi!
I've one more additional question.

Please see attachment.

The orange table shows all the volume discounts of products when you order a certain amount of the materials. For example; if you order 5-9 units, you get 10 euros discount. Can you tell me the formula of column D?

Column A refers to the periods for which you need to have at least the amount of units stated in column B. Column C shows if those required products are ordered together (YES) or not (NO).
Column D should explain what the benefit is if you'd order the units of that certain month together with the previous order. I added the manually calculated results.

[Glenn Kennedy]

Are you sure???

For 18, shouldn't it be

1st 4 @100 0
Next 5 @90 50
Next 5 @80 100
Final 4@70 120

total 270

[liekebreure]

Yes, I'm sure. The new price applies to all units ordered

[Glenn Kennedy]

OK. Next Q. How is Yes/No derived in column C? If you waited till march (24 units) you'd get 40 per unit discount....

[liekebreure]

That's the result of a model. The discount per units is not the only reason to get the condition 'YES' or "NO'. It also has to do with for example storage costs. But in this case we can consider it as 'given'.

[Glenn Kennedy]

OK. Thanks.

[Glenn Kennedy]

I think I have gone in a complete circle and am back where I was at Post 15. I can not see the logic underlying this... so I have no idea what formula to implement.

[liekebreure]

Can I ask you what exactly is unclear? Then I can try to explaint it better

[Glenn Kennedy]

Lets START with C4... it says NO, don't group with previous month... but the expected answer in D4 does the exact opposite combining 6 with the previous month 8...

[liekebreure]

That is true.

It is to check what the benefit WOULD be if you'd order it together with the previous month.
So this condition is only relevant for looking back, not for the month itself.
For example, you check for Feb and March together if it is beneficial. Next, it is said that you should not order Feb and March together by cell C4. Then, you know that for April, you should not take into account Feb, because Feb and March is not ordered together. For April, you check then what the benefit WOULD be if you'd order April together with March.

[Glenn Kennedy]

Sound of Glenn's hair being torn out at the roots...

And the expected results are the last digits in column D??

I just don't get this at all.

[liekebreure]

Yes the expected results are shown in column D

[liekebreure]

Does somebody know how I can enter real-time exchange rates of currencies in Excel 2019?

[Glenn Kennedy]

This is an entirely different question. Start it in a NEW thread.

[liekebreure]

I was searching for that, but I was not able to find it. Where can I start a new thread?

[AliGW]

Go to the Formulas & Functions section, then click on the big, blue button to start a new thread.

https://www.excelforum.com/excel-for...and-functions/

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.