• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Two ways to skin a .....

Lawrence Dodge

Lawrence Dodge

Member
Hi all --
First thank you to SirJB7, without your help on a different problem I would not be this far.
I have a table 10 columns and 25 rows -- odd numbered (*see below for why I am not saying Column A,B,C etc.) columns have a sequential number, while columns have a factor of 0.015 starting at 0.015 and going up. I used the formula
"=IF(E29>3.2,LOOKUP(E29,Protein,INDEX(Protein,0,3)))" With "Protein" being a table. This only worked for the second column but not for columns 4, 6, 8, and 10), so with cut and paste, I refigured the table to only have two columns and presto it works.

My question is how can I re-write the formula so I didn't have to reconfigure the table?

*As a side note, I used a hidden column (hence I couldn't easily say columns B, D, F, etc.) to have the factor and then multiplied each cell by a constant cell -- only way I could figure out how to drag down the formula =$C$1* 1, =$C1$1 * 2, etc. is there a better formula then having the hidden column?

Thanks
L. Dodge
 
Hello good sir.
Here is the modified spreadsheet that works. Originally each section with bold borders was next to each other instead of a long column.
It works I am just wondering how to write better code.
L.Dodge
 

Attachments

"=(OFFSET($A$1,3,1))*((ROW(A1)-1)+1)"

I would have skinned my cat this way if I have plenty of time.

"=$B$4*((ROUNDUP($B$4,0)+ROW(A1))-1)"

Will work if B4 is less than 1.

=$B$4*((COUNTIF($B$4,$B$4))+ROW(A1)-1)

=$B$4*(LEN(REPT(1,ROW(A1))))

=$B$4*(ROWS($A$1:A1) * COLUMNS($A$1:$A$1))

=$B$4*MID(CELL("address",A1),4,LEN(CELL("address",A1)))
 

Attachments

Last edited:
Hi, Lawrence Dodge!

I think that both Excel and me would prefer the actual two column table version, but you can do a bit tricky conversion between N*2 columns table to a 2 columns table. Just a few INDEX, INT, MOD, ROW & COLUMN functions.

But this isn't the main point. My major concern is if the posted table is the actual one (despite of the value in B4 cell); if it is, then Debraj suggestion is the solution: a simple formula!

Regards!
 
ThrottleWorks said:
"=(OFFSET($A$1,3,1))*((ROW(A1)-1)+1)"

I would have skinned my cat this way if I have plenty of time.

"=$B$4*((ROUNDUP($B$4,0)+ROW(A1))-1)"

Will work if B4 is less than 1.

=$B$4*((COUNTIF($B$4,$B$4))+ROW(A1)-1)

=$B$4*(LEN(REPT(1,ROW(A1))))

=$B$4*(ROWS($A$1:A1) * COLUMNS($A$1:$A$1))

=$B$4*MID(CELL("address",A1),4,LEN(CELL("address",A1)))
Click to expand...
whoa -- this is deep. I'm going to have to look at this deeper to see what is going on.
thanks:)
 
You must log in or register to reply here.
Back
Top