=INDEX doens't return value as percentage

[AlexJanetzky]

I have a table such as the one below and this is linked to a dashboard where I have used the following formula to return the value of the last period: =INDEX(BM33:DF33;AANTAL(BM33:DF33)-1) (this works)
In the dashboard I also want to show the value of the previous periode, in this case P2 and have used the following formula: =INDEX(BM32:DF32;AANTAL(BM33:DF33)-1)&": "&INDEX(BM33:DF33;AANTAL(BM33:DF33)-1)

The formula works but it doesn't return the value as a percentage but as "P2: 0,06", and I would want to see "P2: 6,0%)

P1	P2	P3	P4
5,2%	6,0%	8,3%

Can someone please help me with this?

Regards,
Alex

 

[Joe4]

Welcome to the Board!

Once you make it a concatenation (combining multiple values together with an &), it is returning a string, which means formatting has no effect on it (you can only format numeric values).
You can use the TEXT function to apply formatting to that particular part of the calculation, i.e.

=INDEX(BM32:DF32;AANTAL(BM33:DF33)-1)&": "& TEXT(INDEX(BM33:DF33;AANTAL(BM33:DF33)-1);"0,0%")

 

[awoohaw]

Alex, please tell us what language of excel you use? I do not know the AANTAL function.

 

[Fluff]

Hi & welcome to MrExcel.
Try

=INDEX(BM32:DF32,AANTAL(BM33:DF33)-1)&": "&TEKST(INDEX(BM33:DF33,AANTAL(BM33:DF33)-1),"0.0%")

 

[AlexJanetzky]

Alex, please tell us what language of excel you use? I do not know the AANTAL function.

Sorry, I use Dutch. Aantal = Count in English

 

[Joe4]

Sorry, I use Dutch. Aantal = Count in English

Looks like Fluff already figured that out, and the TEXT function in Dutch is actually TEKST.
So his answer is probably the closest, though you may need to change "0.0%" to "0,0%", i.e.

=INDEX(BM32:DF32,AANTAL(BM33:DF33)-1)&": "&TEKST(INDEX(BM33:DF33,AANTAL(BM33:DF33)-1),"0,0%")

 

[AlexJanetzky]

Hi!

Thanks for all the quick replies. I get an error though when I add the suggested ,"0,0%" in combination with the TEKST.

 

[Fluff]

In that case can you post some sample data showing the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[AlexJanetzky]

I got it to work, amazing!

Thanks so much for all the help and quick replies!

 

[Fluff]

Glad we could help & thanks for the feedback.