Autofill horizontally

[JaDiMa]

here is the formula...

=if(A5>$B3,$B3/A5,IF($B3>A5,A5/$B3))

i need it to auto fill as i drag it to the right

when you take a5 by itself, it turns to a6 as you drag it right, that is what i want

but my cell wont do that...

when i drag to the right, i get...

=IF(B5>$B3,$B3/B5,IF($B3>B5,B5/$B3))

[Marcol]

How do you manage this?....

when you take a5 by itself, it turns to a6 as you drag it right

What do you want your fomula to do?

Do you want the result like you would get if you fill/drag down?

[JaDiMa]

if i only have "a5" in a cell by itself, when you drag it right (horizontally), excel auto fills it to a6, and so on... (a7,a8,...)

in my formula excel wants to make it b5 (instead of a6), and it is killing me.

[Marcol]

Okay that is a new one on me! I'm always learning new tricks!!!

I thought you meant dragging =A1 as we were talking about formula.

[JaDiMa]

What do you want your fomula to do?

Do you want the result like you would get if you fill/drag down?

sorry, did not see the other questions...

the formula itself is just a small part of a larger formula. in that particular one, it is just checking to see what cell is greater, and then divides the lower cell by the greater cell.

that formula is dragged down as well, and i get what i want from that. its the horizontal drag that fails. this formula is in a matrix of 400, so i am hesitant to have to type this formula 400+ times.

[Marcol]

hmm

I'm not sure if that can be done by dragging a formula.

If no formula expert answers, would a VBa solution be acceptable to you?

[martindwilson]

a5 on its own will auto increment
=a5 will not it will change to b5
you will have to use index instead what should the next step look like?
when you drag
=if(A5>$B3,$B3/A5,IF($B3>A5,A5/$B3)) across?

[JaDiMa]

i have no VBA talent. i am just a poor man.

[JaDiMa]

a5 on its own will auto increment
=a5 will not it will change to b5
you will have to use index instead what should the next step look like?
when you drag
=if(A5>$B3,$B3/A5,IF($B3>A5,A5/$B3)) across?

i want it to be =IF(A6>$B3,$B3/A6,IF($B3>A6,A6/$B3)) *is praying*


but i get =IF(B5>$B3,$B3/B5,IF($B3>B5,B5/$B3))

[martindwilson]

also
=IF(A5>$B3,$B3/A5,IF($B3>A5,A5/$B3)) what is supposed to happen when a5=b3? as it stands this formula returns FALSE
you probably want just
=IF(A5>$B3,$B3/A5,A5/$B3)
just replace the ref to a5 with
INDEX($A:$A,COLUMN(E1))

[JaDiMa]

also
=IF(A5>$B3,$B3/A5,IF($B3>A5,A5/$B3)) what is supposed to happen when a5=b3? as it stands this formula returns FALSE
you probably want just
=IF(A5>$B3,$B3/A5,A5/$B3)

a5 cannot equal b3. thank the excel gods for that

[martindwilson]

well it still holds so if it's greater do one thing or do something else so

=IF(INDEX($A:$A,COLUMN(E1))>$B3,$B3/INDEX($A:$A,COLUMN(E1)),INDEX($A:$A,COLUMN(E1))/$B3)
that will only increment on filling left to right
do you need a5 to change to a6 on dragging down?

[martindwilson]

how do you get a 400X400 matrix? you only have 256 columns
i think you best post a sample with whats required
1 filling to right and
2 filling down

[Marcol]

Thanks martin I keep getting my fingers burnt with formula ... now there is another one for me to study!

Please Login or Register  to view this content.

Assuming you are using 2007 and not 2003, 2003 has a max of 256 columns

Well that's my tuppence worth

[JaDiMa]

holy smokes!!! i DO only have 256. aaarrgrgrhrhrhrhrh!!! okay, so i am now out of luck. hmmm.

A) i cannot get excel to auto fill horizontally as i wanted to

B) excel has only 256 columns

damn!!! well it is a good thing i didn't type these formulas manually all the way to column 256. whew! thank you soooooo much for that heads up.

so...

A) (for a lesser matrix, i have one that is 242x242) i am unable to get =A5 to be =A6 by dragging horizontally.

B) does Microsoft Access have a larger matrix?

[JaDiMa]

well it still holds so if it's greater do one thing or do something else so

=IF(INDEX($A:$A,COLUMN(E1))>$B3,$B3/INDEX($A:$A,COLUMN(E1)),INDEX($A:$A,COLUMN(E1))/$B3)
that will only increment on filling left to right
do you need a5 to change to a6 on dragging down?

dragging to the right i need =IF(A5>$B3,$B3/A5,A5/$B3) <- i like your abbr formula better

to be =if(A6>$B3,$B3/A6,A6/$B3)

dragging down (which works fine) is...

=IF(A$5>$B4,$B4/A$5,A$5/$B4)

(i have no problem adding or removing $'s before i drag.)

[Marcol]

Excel 2007 has 16384 columns

Can't check the Access 2003 limit I'm on a 2007 machine at the moment.

[martindwilson]

so dragging down in first column always references a5 second column down references a6 and so on?
then use the index function i gave you

[spreadcore]

here is the formula...

=if(A5>$B3,$B3/A5,IF($B3>A5,A5/$B3))

i need it to auto fill as i drag it to the right

when you take a5 by itself, it turns to a6 as you drag it right, that is what i want

but my cell wont do that...

when i drag to the right, i get...

=IF(B5>$B3,$B3/B5,IF($B3>B5,B5/$B3))

All you have to do is Fill your formula down, then set the cell references to absolute's ($)
copy all, delete then use paste special transpose.

Try it on a blank sheet to make sure its the way you want. then copy and paste it into your main sheet.

Excel: Transpose Data

If you have data in columns and you wish that data were in rows (or if you have data in rows that you wish were in columns), you do not need to retype it.

1. Simply select the data and hit the Copy button.
2. Next, select the cell that you want to become the upper left corner of the new range of data.
3. Go to the menu bar and choose Edit | Paste Special. In the Paste Special dialog box, choose Transpose

[JaDiMa]

whew! well i have both 2003 and 2007 excel, so i will just use 2003. i did not realize they had a difference in columns.

so problem B is solved...

Problem A, i am trying that "=IF(INDEX($A:$A,COLUMN(E1))>$B3,$B3/INDEX($A:$A,COLUMN(E1)),INDEX($A:$A,COLUMN(E1))/$B3)" this formula is a bit over my head, but it looks like is working.

[JaDiMa]

All you have to do is Fill your formula down, then set the cell references to absolute's ($)
copy all, delete then use paste special transpose.

Excel: Transpose Data

If you have data in columns and you wish that data were in rows (or if you have data in rows that you wish were in columns), you do not need to retype it.

1. Simply select the data and hit the Copy button.
2. Next, select the cell that you want to become the upper left corner of the new range of data.
3. Go to the menu bar and choose Edit | Paste Special. In the Paste Special dialog box, choose Transpose

ill give this a try. don't see a transpose option, but right now i am on 2003. maybe that's on 2007.

[JaDiMa]

so dragging down in first column always references a5 second column down references a6 and so on?
then use the index function i gave you

thanks martindwilson! you are my hero.

this excel problem is now resolved. qed lol

[JaDiMa]

btw martindwilson, as i am still trying to figure out your formula...

=IF(INDEX($A:$A,COLUMN(E1))>$B3,$B3/INDEX($A:$A,COLUMN(E1)),INDEX($A:$A,COLUMN(E1))/$B3)

what is E1 referring to? it is confusing to me. lol

[martindwilson]

column(a1)=1 column(e1) =5 so when dragged across gives column(e1)....column(g1) an so on incrementing 5 to 6 to 7 upwards
so consider
index(a:a,1) would =A1
index(a:a,2) would = A2
index(a:a,5) would =a5
replace the number with column(e1) ie 5 =a5