Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've searched for a similar question here, but haven't been able to find something that answers my issue.

I'm a mediocre user of Excel 2010 with no experience in macro's. I have a dataset where each row represents a data entry. Let's say each row can be for each of its values (the columns) the maximum or minimum of the entire dataset. How can I create a row at the top where the, for instance, maximum row is shown dynamicly? So when extra data is added to the bottom of the dataset, the new maximum (if applicable) is shown in that row at the top.

enter image description here

So, in this example above, if for instance Brad is added, and Brad has bought for 40 euros of bread and 20 euros of wine, his row is shown/copied/duplicated to row 2, like so:

enter image description here

share|improve this question
    
I'm not an expert here, but do you have much knowledge of pivot tables? Currently there is a good video (1 of 3) on YouTube –  Dave Sep 18 '12 at 10:20

1 Answer 1

up vote 1 down vote accepted

EDITED ANSWER:

Please try this: Where A1 is:

=INDEX(A4:A10;MATCH(B1;B4:B10;0))

and B1 is:

=MAX(B5:B7)

enter image description here

OLD ANSWER:

I am not sure if I understand you correctly but it sounds like

=max(A1:A20)

or

 =min(A1:A20)

is what you need to get the max/min values for your row, where your row starts at A1 and ends at A20. If you add new values to eg. A21 you would need to alter that forula or you could also change the formula to

=max(A1:A1000)

for eg. It is not necessary that you have values in all these cells.

EDIT: Oh i see your problem now but I need to leave. I will reply you later if theres no one faster ;) Maybe have a look at vlookup meanwhile

share|improve this answer
    
Thanks for your answer, but I don't need just the max. or min. I need an entire row to be copied because of one max. or min. value in that row (the question is extended with an example now). –  TimothyHeyden Sep 18 '12 at 9:55
    
Edited my answer. tell me if you have problems. –  Langhard Sep 18 '12 at 12:11
    
It does the job perfectly, and gives me also the opportunity to do that match in the other columns as well! Thank you! –  TimothyHeyden Sep 18 '12 at 12:31
    
You are welcome! –  Langhard Sep 18 '12 at 12:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.