Displaying Currency if Cell >0

[bearcub]

I have a spreadsheet where I have a column with different currency headers. I am only allowed to have one currency per record.

For example, I have the following 5 column headers starting in B1:

US, CA, UK(GBP), FR(EUR)


what I would like to do is create a formula that would determine which column has a value > 0 (an employee is paid a commission). If there is an amount paid I need to add which ledger it impacts (starting in A2).

The region needs to be automatically populated with these results. Those are the ledger names used in our company.

Region       US     CA   UK(GBP)   FR(EUR)  UK(EUR)
US          5000
CA                 300
UK                         200
FR(EUR)                             250
UK(EUR)                                      200

The formula would look across each row to determine which cell in that row is greater than zero (commissions are being paid). It then has to go up to the column header to get a column name so it knows the ledger it impacts (per the example above).

I was toying with index match match but I don't know how I would determine which cell in the lookup range contains a value >0 in the row argument. I need to match the row address to the column header so I know which name to choose.

I was also mulling over the choose function but how to I get the first argument to know the cell address?

I could use a nested if, which I can do but I was wondering if there is a more efficient way of getting this column name.

Appreciate your help in advance,

Michael

 

[sandy666]

maybe try PowerQuery and PivotTable

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Region[/td][td=bgcolor:#5B9BD5]US[/td][td=bgcolor:#5B9BD5]CA[/td][td=bgcolor:#5B9BD5]UK(GBP)[/td][td=bgcolor:#5B9BD5]FR(EUR)[/td][td=bgcolor:#5B9BD5]UK(EUR)[/td][td][/td][td=bgcolor:#DDEBF7]Region[/td][td=bgcolor:#DDEBF7](All)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]US[/td][td=bgcolor:#DDEBF7]

5000​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]CA[/td][td][/td][td]

300​

[/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#DDEBF7]Value[/td][td=bgcolor:#DDEBF7]Attribute[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]UK[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

200​

[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td]

200​

[/td][td]UK(EUR)[/td][/tr]

[tr=bgcolor:#FFFFFF][td]FR(EUR)[/td][td][/td][td][/td][td][/td][td]

250​

[/td][td][/td][td][/td][td]

200​

[/td][td]UK(GBP)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]UK(EUR)[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]

200​

[/td][td][/td][td]

250​

[/td][td]FR(EUR)[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

300​

[/td][td]CA[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

5000​

[/td][td]US[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Region"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]

 

[mumps]

Try: =INDEX($B$1:$F$1,0,MATCH(MAX($B2:$F2),$B2:$F2,0))

 

[bearcub]

Thank you Mumps for the formula. I forgot about the Max function, I was stuck on using the Countif instead which doesn't make any sense. It works great!

I had to modify my code due to the way we name our names.

I was trying to use this formula as a named range but I got an error message because the references are change. The ranges that are relative do not refer to row 2 but the last cell of the spreadsheet. I've had this problem before when I created formulas like this and I realize I'm not doing something correctly when I'm entering the formula.

How do I modify this formula so I can use it as a named range "Currency":

'This is what was entered into the Name Manager:
IF(INDEX($AY$1:$BF$1,0,MATCH(MAX(AY2:BF2),AY2:BF2,0))=$BE$1,$BE$1,
 IF(INDEX($AY$1:$BF$1,0,MATCH(MAX(AY2:BF2),AY2:BF2,0))=$BF$1,"AU",
  IF(INDEX($AY$1:$BF$1,0,MATCH(MAX(AY2:BF2),AY2:BF2,0))=$BC$1,$BC$1,
    LEFT(INDEX($AY$1:$BF$1,0,MATCH(MAX(AY2:BF2),AY2:BF2,0)),2)))))


'This is what happens when I type Currency into the cell:
IF(INDEX('Oct Bookings'!$AY$1:$BF$1,0,MATCH(MAX('Oct Bookings'!AV1048569:BC1048569),'Oct Bookings'!AV1048569:BC1048569,0))='Oct Bookings'!$BE$1,'Oct Bookings'!$BE$1,

  IF(INDEX('Oct Bookings'!$AY$1:$BF$1,0,MATCH(MAX('Oct Bookings'!AV1048569:BC1048569),'Oct 
  Bookings'!AV1048569:BC1048569,0))='Oct Bookings'!$BF$1,"AU",

     IF(INDEX('Oct Bookings'!$AY$1:$BF$1,0,MATCH(MAX('Oct Bookings'!AV1048569:BC1048569),'Oct 
     Bookings'!AV1048569:BC1048569,0))='Oct Bookings'!$BC$1,"AU",

        IF(INDEX('Oct Bookings'!$AY$1:$BF$1,0,MATCH(MAX('Oct Bookings'!AV1048569:BC1048569),'Oct 
        Bookings'!AV1048569:BC1048569,0))='Oct Bookings'!$BC$1,'Oct Bookings'!$BC$1,LEFT(INDEX('Oct 
        Bookings'!$AY$1:$BF$1,0,MATCH(MAX('Oct Bookings'!AV1048569:BC1048569),'Oct 
        Bookings'!AV1048569:BC1048569,0)),2)))))

The references in the IF statements refer to the currency headers as opposed to the actual currency name.

How do I get the relative range ranges to point to the row that the name is entered - do I make the column absolute but the row relative.

Or is there something else that I'm missing in this process?

Thank you for your help,

Michael

 

[bearcub]

Thank you for the idea about PQ but this won't work in this instance this report is being moved to another file for further processing.

Great idea and thank you for the M code.

Michael

 

[mumps]

To be honest, working with formulas is really not my strength. I could try to help if you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.