Return a quarter wise best,poor performed Vehicles name and Values

**Mohammad Munawar** · 09-14-2020, 03:04 AM

Hi,

help required for a formula

I have a data with includes a month and quarter wise performed sum of amounts values of various vehicles.

In this data i need to take out a results of Quarter wise best/Poor performed Vehicles names and the return a incurred positive or negative amounts against the vehicle.

At last need to be specify overall best/ performed vehicle names and the against that show the values generates.

sample enclosed for the reference.

Thanks,

**Bo_Ry** · 09-14-2020, 03:48 AM

Please try at
C12:C13
=INDEX($A$4:$A$8,MAX(ROW($A$4:$A$8)*(INDEX($B$4:$H$4,MATCH(B$11,$B$1:$J$1,)):INDEX($D$8:$J$8,MATCH(B$11,$B$1:$J$1,))=D12))-ROW($A$3))

D12
=SUBTOTAL({4;5},INDEX($B$4:$H$4,MATCH(B$11,$B$1:$J$1,)):INDEX($D$8:$J$8,MATCH(B$11,$B$1:$J$1,)))

Copy C12:D13 to F12, I12

E16:E17
=INDEX($A$4:$A$8,MAX(INDEX(ROW($A$4:$A$8)*($B$4:$J$8=F16),))-ROW($A$3))

F16
=SUBTOTAL({4;5},B4:J8)

**bebo021999** · 09-14-2020, 04:01 AM

How to decide what vehicle is best or poor? Total Jan to Mar?
For 1st quarter, total of each kind of vehicle is {-6740,-7541,-11955,-7481,-5807}
which is best, -11955 (Pick up) or -5807 (Crane)?

**bebo021999** · 09-14-2020, 04:23 AM

Try with non-array solution:
C12

Please Login or Register  to view this content.

D12

Please Login or Register  to view this content.

C13

Please Login or Register  to view this content.

D13

Please Login or Register  to view this content.

Copy C12:D13 to the right

See attachment

**Mohammad Munawar** · 09-14-2020, 04:50 AM

Thanks a lot for you all for your valuable responses,

Actually I am looking quarterly wise full sum of values and the same for Overall to be decide the which vehicle are performed best or poor. I regret if I am not explaining this point in my previous post.

Appreciate for provide to this like solution as above said,

which is best, -11955 (Pick up) or -5807 (Crane)?

Answer:-if negative values incurred, which value having a minimum negative number that would be the best because of minimum loss.

**Bo_Ry** · 09-14-2020, 05:56 AM

Please try at
C12

Formula:

Please Login or Register  to view this content.

copy to F12, I12

E16

Formula:

Please Login or Register  to view this content.

**Mohammad Munawar** · 09-14-2020, 06:47 AM

Thanks a lot dear,

its working brilliantly.

Return a quarter wise best,poor performed Vehicles name and Values

LinkBack

Thread Tools

Rate This Thread

Display

Return a quarter wise best,poor performed Vehicles name and Values

Re: Return a quarter wise best,poor performed Vehicles name and Values

Re: Return a quarter wise best,poor performed Vehicles name and Values

Re: Return a quarter wise best,poor performed Vehicles name and Values

Re: Return a quarter wise best,poor performed Vehicles name and Values

Re: Return a quarter wise best,poor performed Vehicles name and Values

Re: Return a quarter wise best,poor performed Vehicles name and Values

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How to return last quarter months?

[SOLVED] Formula to look at a date and return a quarter (i.e., Q1, Q2, Q3, Q4)

need formula to get same result from total as per Quarter wise

[SOLVED] Formula performed across all columns to return value in a single cell

[SOLVED] need to arrange column wise data to row wise with monthly wise

Index value return based on batch no wise

[SOLVED] I want change the data row wise to coloum wise & coloumn wise to row wise.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions