Compare values from a column between them

[romeo.malancu.m]

HI,

I'm trying to build up a formula that has to compare all the values in that column, but when it does the comparison it should compare it like this: A1 against A2(-/+3%), A1 against A3(-/+3%), ....,A1 against A1500(-/+3%). After comparing A1 against all the cells in that column it should display if it found any results and on which row (maybe more than one).
To understand better my problem: i'm collecting data from a photovoltaic plant. Each hour i have the following values: Hour, Production, Temperature and Radiation. What i try to establish is: if and how is affected the pruduction for the same given conditions of Temperature and Radiation. For this i have to find the pair of data (Temperature and Radiation) and display The production. Since the temperature varies from -30 to +60(with teo decimals) and the radiation varies from 0 to 1500 (with two decimals), it is very hard to find two pairs that are in 3% marge of each other. I also have to mention that i have a 16000 row table. Hope someone can give me a start. i don't mind breaking the steps of the calculation in several other tables.

Thank you

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[romeo.malancu.m]

In attachment a sample of what i try to do. Thank you

[AliGW]

Please check your attachment - it is reporting as not a valid file type.

[romeo.malancu.m]

i have successfully downloaded my own attachment. It is a xls simple attachment

[AliGW]

Unable to here, sorry, so I am not able to help.

[romeo.malancu.m]

Perhaps it was my mistake. The data from that file was linked to other excel table. i attached now one without any external references.

[JeteMc]

This proposed solution employs a helper table to find only the rows of data that have a temperature within the prescribed variance (+/- 3%). The table is populated using the following array entered formula*:

=IF(ISERROR(INDEX(A$2:A$745,SMALL(IF($C$2:$C$745<=$Q$1*(1+$N$1),IF($C$2:$C$745>=$Q$1*(1-$N$1),ROW(A$2:A$745)-1)),ROW(A1)))),"",INDEX(A$2:A$745,SMALL(IF($C$2:$C$745<=$Q$1*(1+$N$1),IF($C$2:$C$745>=$Q$1*(1-$N$1),ROW(A$2:A$745)-1)),ROW(A1))))

The second table could be used as the final result table if the target temperature and radiation were put in another location. The second table is populated by an array entered formula* that is similar to the first, however it draws data only from the first table.
*Array entered formulas are activated by simultaneously pressing the Ctrl, Shift and Enter keys while the cell containing the formula is in edit mode.
Let us know if you have any questions.

[romeo.malancu.m]

Thank you for your interest shown in helping me. From what i understand (im not an excel wizz) the value 10 for temperature and the value 13 for radiation are fixxed values (ive chosen these values for the example).
Actualy, normaly i do not know what the first temperature the formula will find. The formula(s) should compare A1 with all the rest of the cells in the A column, if it find a match should go on in showing the matches and do what you have done in the second table. If it does not find matches, it should go to the next step in comparing A2 against the cells from A3 down, then compare A3 againts the cells from A4 down and so on. I realy do not know if it can be done in excel. Would be nice.

[JeteMc]

What you are describing sounds like a loop:
1) Find the ith temp and radiation level
2) Find all temps that are within a variance of 3% of the initial temp
3) From that list find all radiation levels that are within a variance of 3% of the initial radiation level
4) If there are additional records meeting the parameters display them
5) If there are no additional records meeting the parameters iterate i and go back to step one.
I believe that doing that would require VBA.
I would suggest starting a new thread on the VBA forum at this site (Excel Forum). In your post include a link to this thread so that volunteers can get a sense of what you want to accomplish.
Let us know if you have any questions.

[romeo.malancu.m]

That is exactly what i want to accomplish. I will do that, post on VBA forum my issue. Thank you very much.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark this thread as 'Solved' (since it appears to have gone as far as it can) using the thread tools link above your first post. I hope that you have a blessed day.