Displaying Currency if Cell >0

bearcub

Well-known Member
Joined
May 18, 2005
Messages
731
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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.

Code:
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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]


Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Region"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"[/SIZE]
 
Last edited:
Upvote 0
Try: =INDEX($B$1:$F$1,0,MATCH(MAX($B2:$F2),$B2:$F2,0))
 
Upvote 0
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":

Code:
'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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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