Help with TEXT function

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have the following formula which I want to format to 0.0% and am having
difficulties doing same. Without the formatting it is the following and
works

="Sales are
"&(IF(ISERROR(SUMPRODUCT(--(SalesDateLY=(D62-Home!AC$3)),(NetSalesHOBOLY+VATHOBOLY))),0,(D68/((SUMPRODUCT(--(SalesDateLY=(D62-Home!AC$3)),(NetSalesHOBOLY+VATHOBOLY)))/100)))-1)

With the formatting included I thought is was the following, but I am
getting an error

="Sales are
"&(IF(ISERROR(SUMPRODUCT(--(SalesDateLY=(D62-Home!AC$3)),(NetSalesHOBOLY+VATHOBOLY))),0,TEXT((D68/((SUMPRODUCT(--(SalesDateLY=(D62-Home!AC$3)),(NetSalesHOBOLY+VATHOBOLY)))/100)))-1),"0.0%")

Any ideas?
 
Got it, needed to put TEXT before the first arguement, as in

="Sales are
"&TEXT((IF(ISERROR(SUMPRODUCT(--(SalesDateLY=(D62-Home!AC$3)),(NetSalesHOBOLY+VATHOBOLY))),0,D68/((SUMPRODUCT(--(SalesDateLY=(D62-Home!AC$3)),(NetSalesHOBOLY+VATHOBOLY)))/100)))-1,"0.0%")
 
Do you need the text as part of the cell content? If not, you can leave
your formula and choose

Format/Cells/Number/Custom "Sales are "0.0%
 
Thanks JE

Never knew you could actually format with text like that, perhaps I don't
need such a complicated formula
 
Back
Top