Test multiple variables

[I.am.Rustam]

Hi Guys!

I tried this in the Excel General and got no help... trying it here! Please help if you could.

I made a screenshot of the table that I need to build based on the variables in the columns BC, BD, BE.

Excel Forum.jpg


I also attached the excel spreadsheet.
P.S. colors don't need to be there, I just included them for easier visual presentation.

Thank you!

[Olly]

How are you comparing Y-values? There are no exact matches...

You have duplicate X & Y values in BC11:BD12, but different Years for each pair... which should be chosen, and how are you choosing?

[I.am.Rustam]

Olly, thanks for you respond!.Yes, that is the challenge I have.

Yes, my Y variables with decimal numbers. If Y value is 3.7 then it takes the 4th column... even if it is 3.1. Once it is greater than the whole number, it should be pasted in the next column.

I do have duplicates, I can't get rid of them since they are different (in terms of the clients). So, I need to include both of them... I need to take BD11 value (3.73) test it until it false, then go to BD12, add BD11 and BD12 together (3.73+3.73) and test it going forward pasting corresponding value of the BD 12 in Column BE 12.

I hope I am not confusing...

Thank you!

[Olly]

I think I understand...

So, do you deal with the sum of the rounded up values, or the rounded up sum of the values?! e.g. if 3.1 was followed by 3.1... the first year would display in columns 1-4... would the second year display in columns 5 - 8, or 5 - 7?

[I.am.Rustam]

Good point Olly! Ideally, I would like to see it in 5-8 (if possible of course). If you know the way to round them up first and then test it, it would be great!

Many thanks!

[Olly]

Okay - I've added 3 helper columns to your data table (and converted it to a Table, for ease of referencing). Then used a 'sumifs' to retrieve the relevant year. It matches your expected results.

See attachment.

[nilem]

or maybe so

Please Login or Register  to view this content.

[I.am.Rustam]

Olly,

That's very smart! Thanks a lot for your time and effort! Works great!

[I.am.Rustam]

Nilem,

These codes like science to me But it works!
What should I add for macro to update automatically?

Thanks again