pick negative figures with all related columns data

Z

Zubair

Active Member
Joined
Jul 4, 2009
Messages
304
Office Version
  1. 2016
Platform
  1. Windows
Hi

I have marked the following thread as solved so raising new in continuation of the same with some modification.

www.mrexcel.com

pick negative figures with all related columns data

I have a huge data with many years and products, from that I want to pick only negative figures and there corresponding row/column data, please help with formula. DATA Product Month Volume GM CPL Product A Feb 12 14.00 1.17 Product B Jan 13 15.00 1.15 Product B Mar 14 (15.00) (1.07)...
www.mrexcel.com www.mrexcel.com

In the "Result" tab I have made Summary structure as follows (reduced many column) and wanted to apply same formula with some modification.

1. Tab "Results" A1 to C1 I have applied data validation with list Data!Z1:Z24 copy/paste value transposed from Data!A1:X1, wanted data to come based on particular selection A1 = Area, B1 Description, C1 = Month and fixed headings D1:H1.

2. Tab "Result" J2 I put year 2021 for current year and will change 2022 for next year and so on.

3. Tab "Result" K2 for management review if they put 0 means all information required based on negative GM CPL, if 5 means less than or equal to 5 GM CPL.


Result
AreaDescriptionMonthVolumeTurnover US$Std Cost US$Std Gross Profit US$Sum of GM CPLYearCPL review <=
Area Manager 1SetJet 50 CD BulkJan2,000114,918189,448(74,530)(37.26)20210
Area Manager 1SetJet 50 CD 1 ltrFeb4,563305,906463,190(157,284)(34.47)
Area Manager 1SetPlus 20W50 CF4 1 ltrMar40727,72041,101(13,381)(32.89)
Area Manager 1SetJet 50 CD BulkApr2,000114,918189,448(74,530)(37.26)
Area Manager 1SetJet 50 CD 1 ltrJan6,676447,513677,606(230,093)(34.47)
Area Manager 4BetJet 20W50 CNG CD 4 ltrFeb5,523566,690587,702(21,012)(3.80)
Area Manager 5BetJet 20W50 CNG CD 4 ltrMay23,9422,456,6172,547,703(91,086)(3.80)
Area Manager 5BetJet 20W50 CNG CD 4 ltrJun3,093317,331329,096(11,766)(3.80)
Area Manager 7NetJet 5W30 SG 5 ltrJul2,174182,463231,377(48,915)(22.50)
Area Manager 9NetJet 5W30 SG 5 ltrJan8,403805,908894,211(88,303)(10.51)


Data
BusinessActual/PlanAreaSectorItemDescriptionProduct FamilyPackIMP/LocalYearQuarterMonthYTD No.VolumeGSV PKRRebateDiscountTurnover PKRStd Cost PKRStd Gross Profit PKRTurnover US$Std Cost US$Std Gross Profit US$GM CPLBusiness
Region 1ActualsArea Manager 1Segment 5PKG02010000SetJet 50 CD BulkSet1Loc2021Q1Jan902,000898,800(467,376)431,424711,224(279,800)114,918189,448(74,530)(37.26)Actual/Plan
Region 1ActualsArea Manager 1Segment 1PKG02010001SetJet 50 CD 1 ltrSet1Loc2021Q1Feb904,5632,392,565(1,244,134)1,148,4311,738,908(590,477)305,906463,190(157,284)(34.47)Area
Region 1ActualsArea Manager 1Segment 2PKG02020001SetPlus 20W50 CF4 1 ltrSet1Loc2021Q1Mar90407216,808(112,740)104,068154,303(50,235)27,72041,101(13,381)(32.89)Sector
Region 1ActualsArea Manager 1Segment 4PKG02010000SetJet 50 CD BulkSet1Loc2021Q1Apr902,000898,800(467,376)431,424711,224(279,800)114,918189,448(74,530)(37.26)Item
Region 1ActualsArea Manager 1Segment 5PKG02010001SetJet 50 CD 1 ltrSet1Loc2021Q1Jan906,6763,500,109(1,820,057)1,680,0532,543,867(863,815)447,513677,606(230,093)(34.47)Description
Region 1ActualsArea Manager 4Segment 2PKG03020004BetJet 20W50 CNG CD 4 ltrBet4Loc2021Q1Feb905,5234,432,223(2,304,756)2,127,4672,206,349(78,882)566,690587,702(21,012)(3.80)Product Family
Region 1ActualsArea Manager 5Segment 2PKG03020004BetJet 20W50 CNG CD 4 ltrBet4Loc2021Q1May9023,94219,213,812(9,991,182)9,222,6309,564,586(341,956)2,456,6172,547,703(91,086)(3.80)Pack
Region 1ActualsArea Manager 5Segment 2PKG03020004BetJet 20W50 CNG CD 4 ltrBet4Loc2021Q1Jun903,0932,481,921(1,290,599)1,191,3221,235,494(44,172)317,331329,096(11,766)(3.80)IMP/Local
Region 2ActualsArea Manager 7Segment 2PKG04010005NetJet 5W30 SG 5 ltrNet5Loc2021Q1Jul902,1741,630,954(456,667)(489,286)685,001868,636(183,635)182,463231,377(48,915)(22.50)Year
Region 2ActualsArea Manager 9Segment 3PKG04010005NetJet 5W30 SG 5 ltrNet5Loc2021Q1Jan908,4036,303,205(3,277,667)3,025,5383,357,046(331,508)805,908894,211(88,303)(10.51)Quarter
Month
YTD No.
Volume
GSV PKR
Rebate
Discount
Turnover PKR
Std Cost PKR
Std Gross Profit PKR
Turnover US$
Std Cost US$
Std Gross Profit US$
GM CPL
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
You must log in or register to reply here.

Similar threads

Z
  • Solved
Sumifs & rank combined
Replies
7
Views
287
Peter_SSs
Peter_SSs
Z
  • Solved
Selection of specific information from data sheet
Replies
5
Views
299
maabadi
M
Z
  • Question
Issue in Index formula
Replies
2
Views
256
Zubair
Z

Forum statistics

Threads
1,214,819
Messages
6,121,741
Members
449,050
Latest member
excelknuckles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top