Forum Discussion

Copper Contributor

May 22, 2020

Solved

Gradient Tool

Hello I am using Excel for this and a custom function, my problem is the functions between I8 and AV9 altought some of them appear to work, sometimes they are giving me the Value Error. I think this ...

Gradient.xlsm41 KB

excel

Formulas and Functions

Macros and VBA

mtarler
May 22, 2020
IsaacPiscopo yes sort of. Your formula for grabbing the RIGHT x characters is looking at how many characters are in the 1st section of the RGB code. So you had 3 characters for red so it was taking the last 3 characters instead of only 1. Here is an updated formula:
=IF($E$11>=I6-2,"#"&DEC2HEX((LEFT(I7,SEARCH(" ",I7,1)-1)),2)&DEC2HEX((MID(I7,SEARCH(" ",I7)+1,SEARCH(" ",I7,SEARCH(" ",I7)+1)-SEARCH(" ",I7)-1)),2)&DEC2HEX((RIGHT(I7,LEN(I7)-SEARCH(" ",I7,SEARCH(" ",I7)+1))),2),(""))

I also updated the initial condition to $E$11 and compared it to the numbers above it I6-2 so it would be easy to copy/fill and have excell automatically update all the cells. That said, you MERGED all those cells which disables may useful things in excel. If you want wider cells I recommend just highlighting those columns and dragging them wider or right click and select 'column width'
I attached an updated file but never enabled the macros so they may or may not be attached.
Copy of Gradient.xlsm46 KB

Jos_Woolley

Iron Contributor

May 22, 2020

IsaacPiscopo

Just to add to mtarler's post, if you have Office 365 then the formula in I8 can be abbreviated to:

=IF($E$11>=I6-2,"#"&CONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7," ",REPT(" ",25)),{1,26,51},25)),2)),"")

Regards

mtarler
Silver Contributor
May 22, 2020
Jos_Woolley Even without the new array functionality you might be able to use that as an array function or at least use that trick to "simplify" the existing formula to avoid the search of search - search ...
=IF($E$11>=I6-2,"#"&CONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7," ",REPT(" ",25)),1,25))&CONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7," ",REPT(" ",25)),26,25))&CONCAT(DEC2HEX(TRIM(MID(SUBSTITUTE(I7," ",REPT(" ",25)),51,25)),2)),"")
- Jos_Woolley
  Iron Contributor
  May 22, 2020
  mtarler
  
  Actually my suggestion had nothing to do with the new array functionality within 365. It works as a standalone, single-cell formula. My reference to 365 was based solely on the use of the CONCAT function, which I (perhaps erroneously?) believed was only available to 365 subscribers.
  
  P.S. Why are you using CONCAT in the construction you posted? Seems superfluous to me.
  
  Regards
  - mtarler
    Silver Contributor
    May 22, 2020
    Jos_Woolley you are correct.
    1) I didn't recognize any of those functions as inherently being array functions that would work with that array input {1, 26, 51} but apparently it works fine (at least in my excel)
    2) concat is wasteful because I was lazy and quick to copy and paste your formula 3 times to avoid the {…} array portion.