Dear all,
the attached sheet is currently working well, but for a self improvement, I would like to have some recommendations on how to improve it/make it shorter, and thus have new inspirations for further works.
Sheet explanation :
B2:B4 looks up A2:A4 corresponding to F:F
Puts "," every thousands
links G and H to be "price 1 - price 2"
adds JPY mark if B6 rate is 1.
divides, rounds up and adds USD or EUR mark corresponding on the B6 rates.
The current formula is :
Formula:=CONCATENATE(IFS(B$6<=1,TEXT(ROUND(XLOOKUP(A2,F:F,G:G)/B$6,0),"###,###")&"¥ - ",
AND(B$6>=100,B$6<=120),TEXT(ROUND(XLOOKUP(A2,F:F,G:G)/B$6,0),"###,###")&"$ - ",
AND(B$6>=121,B$6<=150),TEXT(ROUND(XLOOKUP(A2,F:F,G:G)/B$6,0),"###,###")&"€ - "),
IFS(B$6<=1,TEXT(ROUND(XLOOKUP(A2,F:F,H:H)/B$6,0),"###,###")&"¥",
AND(B$6>=100,B$6<=120),TEXT(ROUND(XLOOKUP(A2,F:F,H:H)/B$6,0),"###,###")&"$",
AND(B$6>=121,B$6<=150),TEXT(ROUND(XLOOKUP(A2,F:F,H:H)/B$6,0),"###,###")&"€"))
I am all open to suggestions.
Bookmarks