Software

Ask something
Question Locked

Excel Question I need help....

0 helpful
4 replies

by macki60

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I submit my report in excel and I am having a hard time using the mixed referrence function...

This is an example
1. I have data A1, A2, A3....down a column.
2. I have one value on C2 ( constant value)
3. the formula is a very simple multiplication formula. I want to multiply every single value of column A to C2. But it needs to be in across a row that starts in D4. So,(D4, E4, F4)
4. So when I input =$A1*$C$2 in D4 it calculates it fine but when i try to drag the formula across the row so [E4=$A2*$C$2, F4=$A3*$C$2...] it repeates the same fomula as D4 and does not adjust for the increase in column number.

How can I fix it so when I drag the formula across a row it can still account for the mixed reference?

Please help and thank you.

4 replies

by thumbsup2

If you drag across, it will copy across, not down. It doesn't know any better. You will have to change the cell references manually.

by victor.gutzler

Try using the following formula in cell D4: =$C$2*INDIRECT("A" & CELL("col",D4)-3,TRUE). This formula references the current cell's column number, offsets it by 3, and gets the value from the contrived cell address. The formula also adjusts itself to cells it is copied to.

This discussion is closed to new replies.