Need HELP with my INDEX formula

E

EmileEA

New member
Joined
Jun 26, 2014
Messages
13
Reaction score
0
Points
0
Hi all,

I was using an index formula for a few weeks for 7 different products (all seperated excel files).

Now after a few weeks using the index formula it stopped working for one product. 3 weeks later for another one and now this week again.
I have no clue why it randomly stops working after a few weeks. The week it stops varies for every product some I have already managed for 30 weeks and then stopped, for the other file it stopped after 8 weeks.

It costs me a lot of time to do it manually and therefore I need your help.

I'm using this formula

=IFERROR(ROUNDUP(INDEX(Input!17:18;$G$7;MATCH($D$2;Input!$4:$4;0)-1);0);0)

I made an attachment excel file for more specific details.

Could anyone give me a likewise formula so I can retrieve the same data?

I would be very very very very very thankfull!

Thanks
 

Attachments

  • ST Progress product X.xlsx
    20 KB · Views: 14
I am not sure that you need to use another "better" formula. That one you have is probably the best for this job.

Are you sure that your Automatic calculations is turned on (in the Formulas Tab, Under Calculations Section)?
 
Well, unless then you are mixing somehow the formats and entering numbers as text, then I don't see why you would have that problem.

What exactly do you mean by "stopped working"? Is the result wrong, is it giving an error? is it simply not changing the value upon change in parameters?
 
it gives a 1 or a 0 as a result. Which is very odd.
 
I can see maybe getting a 0, but not a 1. Is it happening to you right now? Are you able to show a current instance of the problem occurring?
 
Here you have the attachment with a full time line.

The problem occurs after calenderweek 16. If you switch it to 17 and beyond the numbers get odd
 

Attachments

  • ST Progress Y.xlsx
    26.6 KB · Views: 14
Last edited:
Click Go Advance under the Quick Reply box. Then hit the paperclip icon and do as you did in your first post.
 
In your row 4 you have 2 9's, the first one is at K4, the second one is at AJ4. Match is finding the first occurrance, ie K4 and trying to return what is in J32, so it is returning 49.3% (0.493....) and rounding that up to 1.

I see that you have 08 entered as text for week 8. If you do the same for week 9, then it will work for that week, but then you will have problems with weeks 10, 11, 12, etc. unless you enter them as text (ie. prefix with an apostrophe). The other option is to combine Week and week number into one cell, eg. in G4 enter 'Week 8' all in one cell, etc.
 
Wauw many thanks NBVC! I understand the problem now.

But to be honoust I'm a excel noob and do not know what the best solution is for me. You are saying I should combine it as Wk8 wk9 wk10 etc. am I correct?
 
Yes. That is what I am saying. This will eliminate the confusion between the Week numbers and the "weeks since release number" which appear in the same row.

Another alternative is to separate the week and weeks since release number into separate row headers if that works for you.
 
Many thanks for your Help NBVC! I will work this out tommorow and you really made my day. If I don't come back at this forumthread in the next couple of days it means that you fixxed my problem!

Many thanks!
 
You must log in or register to reply here.
Back
Top