Forum Discussion
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 is happening because when the functions between A7 and AV7 provide the RGB codes they provide 255 8 255 instead of 255 008 255 and I need to add functions to the function to add the 0's.
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.
12 Replies
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
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)),"")
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
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.
So I changed all the boxes and yeah now the RGB box is broken. I also changed the * part to cell boxes so its better for autofill.(btw I'm using Microsoft Office 365, not to be confused with Microsoft 365)
IsaacPiscopo I believe that formula was also incorrect. At the very least I noticed you had the same error looking at the "right" part of the code but using the left part to determine the number of characters to find.
That said, I think the problem you were seeing was something with your 'math'. I didn't understand what math you were exactly doing so I also changed it to be a weighted average for each color portion.
Although I think the neat trick that Jos_Woolley used could have made the formula more simple, I kept your format since that is what you understand.
