I have a problem not being able to format numbers after they will filled using INDEX MATCH:
Formula:
Please Login or Register to view this content.
It pulls in the correct numbers but some are displayed with many decimal places even though the source number is formatted to 2 decimal places and the column where the results are has been formatted to 2 decimal places.
Does anyone know why?
Thank you
1. you dont need to enter that formula as an ARRAY, just enter regularly.
2. what does the source data look like - is it numeric of text (and where does it come from?)
test with =ISNUMBER(cell-ref)...FALSE indicates TEXT and you will need to convert
=IFERROR(--INDEX(Sheet1!H$3:H$12&"",MATCH(Sheet2!$A3,Sheet1!$G$3:$G$12,0)),"")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Source data is number and set as 2 decimal places. Thank you for the help
Actually the source data was only formatted to look as if it had two decimal places. The issue traces back to the formula in Sheet1!E3 and down. Paste the following formula into Sheet1!E3 and double click the fill handle, then check on sheet 2 to see that the numbers now display as desired:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Why does your formula concatenate empty string onto the column H values -- effectively converting those numbers to text strings? My first thought would be to remove that concatenation from the formula, so that the formula will return numbers and not text.
Originally Posted by shg
Change your formula to non-arrayed
Formula:Please Login or Register to view this content.
You were converting the number to text with the &"" !
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
That works thank you very much. I didn't know it traces back that way for format.
Thanks to everyone helping.
The &"" converted it to text like ChemistB mentioned and going back to the Sheet1!E3 makes a difference too like JeteMc mentioned.
Cheers!
Last edited by BillyRaySpivy; 02-09-2017 at 03:24 PM.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. Just a short note, formatting is just the way that Excel displays a number, it doesn't change the value of the number. For example, download the workbook attached to post #3 and select Shee1!E11. Now run Evaluate Formula from the Formulas tab. You'll see that the result of dividing C11 by D11 has 15 decimal places, whereas it is only displayed as a number with two decimal places. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks