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

1. ## Reference to cell with numbers returns other numbers than in the original cell

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.

2. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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.

3. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

4. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

5. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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.

6. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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.

7. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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.
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!

8. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

9. ## Re: Reference to cell with numbers returns other numbers than in the original cell

Thanks Glenn!

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!

10. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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?

Hi Glenn,

13. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

14. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

15. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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.

16. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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?

17. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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.

18. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

19. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

20. ## Re: Reference to cell with numbers returns other numbers than in the original cell

Hi Glenn,

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

21. ## Re: Reference to cell with numbers returns other numbers than in the original cell

Try:

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

22. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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.

23. ## Re: Reference to cell with numbers returns other numbers than in the original cell

Sorry, this was out of time!

24. ## Re: Reference to cell with numbers returns other numbers than in the original cell

Originally Posted by liekebreure
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.

25. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

26. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

28. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

29. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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.

30. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

31. ## Re: Reference to cell with numbers returns other numbers than in the original cell

Thank you so much!! This is amazing

32. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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.

33. ## Re: Reference to cell with numbers returns other numbers than in the original cell

Hi!

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.

34. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

35. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

36. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

37. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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'.

OK. Thanks.

39. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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.

40. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

41. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

42. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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.

43. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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.

44. ## Re: Reference to cell with numbers returns other numbers than in the original cell

Yes the expected results are shown in column D

45. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

46. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

47. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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

48. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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/

49. ## Re: Reference to cell with numbers returns other numbers than in the original cell

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)