Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-21-2019, 06:57 AM
Phil H Phil H is offline Concatenating in an Array formula Windows XP Concatenating in an Array formula Office 2010 64bit
Advanced Beginner
Concatenating in an Array formula
 
Join Date: Jun 2010
Posts: 81
Phil H is on a distinguished road
Default Concatenating in an Array formula


How to concatenate " (24%) " into this array formula?

{="After-Tax Balance " & TEXT(INDEX(AH:AH,MAX(IF(AH5:AH200="",FALSE,ROW(AH5 :AH200)))), "$#,##0")}

Result now is: After-Tax Balance $100
Desired: After-Tax Balance $100 (24%)
Reply With Quote
  #2  
Old 02-21-2019, 07:17 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Concatenating in an Array formula Windows 7 64bit Concatenating in an Array formula Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,411
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

Post a sample file. thx
Reply With Quote
  #3  
Old 02-21-2019, 07:25 AM
Phil H Phil H is offline Concatenating in an Array formula Windows XP Concatenating in an Array formula Office 2010 64bit
Advanced Beginner
Concatenating in an Array formula
 
Join Date: Jun 2010
Posts: 81
Phil H is on a distinguished road
Default

Sorry, Pecoflyer, the file has confidential and personal identifiable information. Maybe I have to dismiss this post?
Reply With Quote
  #4  
Old 02-21-2019, 09:08 AM
xor xor is offline Concatenating in an Array formula Windows 10 Concatenating in an Array formula Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,041
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Is (24%) just something you enter and not the result of another formula? If Yes, try:

="After-Tax Balance " & TEXT(INDEX(AH:AH,MAX(IF(AH5:AH200="",FALSE,ROW(AH5 :AH200)))), "$#,##0")&" (24%)"

Array-entering not necessary.

(By the way - never use full column references in array formulas, that's really bad practice).
Reply With Quote
  #5  
Old 02-25-2019, 05:24 AM
Phil H Phil H is offline Concatenating in an Array formula Windows XP Concatenating in an Array formula Office 2010 64bit
Advanced Beginner
Concatenating in an Array formula
 
Join Date: Jun 2010
Posts: 81
Phil H is on a distinguished road
Default

xor, Thanks for your help. Yes, this is simply a notation of a tax bracket. Tried it as you suggested, but the only way it would work is as an array formula. Regarding your comment on not using column references, how should the formula be stated?
Reply With Quote
  #6  
Old 02-25-2019, 05:46 AM
xor xor is offline Concatenating in an Array formula Windows 10 Concatenating in an Array formula Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,041
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

Yes you are right about array formula.

="After-Tax Balance " & TEXT(INDEX($AH$5:$AH$200,MAX(IF($AH$5:$AH$200="",F ALSE,ROW($AH$5 :$AH$200)))), "$#,##0")&" (24%)"
Reply With Quote
  #7  
Old 02-25-2019, 08:36 AM
Phil H Phil H is offline Concatenating in an Array formula Windows XP Concatenating in an Array formula Office 2010 64bit
Advanced Beginner
Concatenating in an Array formula
 
Join Date: Jun 2010
Posts: 81
Phil H is on a distinguished road
Default

xor, This works too - without array entry. Thanks for the education... R/Phil
Reply With Quote
Reply

Tags
array, concatenate

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array formula value not available error for one result emarkona Excel 1 03-08-2018 03:00 PM
Array Formula Macacidic Excel 2 09-12-2017 08:19 AM
Concatenating in an Array formula Excel simplify formula - array of sequential numbers Wries Excel Programming 1 05-12-2014 08:32 AM
Concatenating in an Array formula Convert String Array to Integer Array from a User Input? tinfanide Excel Programming 4 12-26-2012 08:56 PM
Concatenating in an Array formula Complex array formula andrei Excel 9 02-03-2012 03:40 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 05:38 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft