Can excel automatically tweak formulas?

Hi,

Can someone please tell me if Excel has a function which can automatically tweak the same formula multiple times in an ascending order? in other words, can excel tweak the same formula which is in the same row but in multiple columns which are in multiple charts (see attached spread sheet), by each time tweaking one or two characters in the formula in an ascending order? For example (for some reason the system does not highlight in color the numbers I need tweaked each time. They are the two digits at the end part of the formula) - formula 1 in chart 1 would be =COUNTIFS($C$1:$C$19000, 0, $C$2:$C$19000, 0, $C$2:$C$19001, E3) , formula 2 in charts 2 would be =COUNTIFS($C$1:$C$19000, 0, $C$3:$C$19002, H3) , formula 3 in charts 3 would be =COUNTIFS($C$1:$C$19000, 0, $C$4:$C$19003, K3) , formula 4 in charts 4 would be =COUNTIFS($C$1:$C$19000, 0, $C$5:$C$19004, N3)

I hope excel can do this, since to do it manually is mind numbing.

Many thanks,

JackBlack

Your sample is basically empty, can you post something with formulas and charts in it and explain what part in the sample needs to update (by referring to exact cells, columns, rows, arguments in a formula)?

There are many ways you may be able to accomplish what you want, knowing the best way would require us to know a bit about your layout and what you want to accomplish in more detail.

You could for example combine INDIRECT with something like the ROW or COLUMN functions. All depends on what the goal is.

I have attached a spreadsheet with the exact formulas I need tweaked. As mentioned, I need to continue those charts up to chart 100 and each time I need to change two digits in the second part of the formula in the ascending order which I stated - 2, 19001 / 3, 19002 / 4, 19003 / 5, 19004 etc all the way up to 101, 19100

If you just make them relative instead of objective range references, shouldn't they automatically change when you pull the functions down?

Originally Posted by jackblack2

(this e-mail system you use sucks)

Protip: insulting the board software is rarely going to make people want to help you for free, especially if you apparently don't understand the difference between "email" and "forum"

Originally Posted by jackblack2

since one cannot drag a formula left or right.

Sure it can, You can fill formulas in any direction and columns and rows can be absolute or relative. You do so in the exact same way you fill down or up.

...No, you can use it to pull-and-drag left, right, up, or down. (It can erase if you're pulling in towards the original cell, but it's pretty intuitive once you've used it a few times).

Double-clicking it will dropfill, where it will fill down automatically along a column of data adjacent to it.

If you select a single cell you can fill in any direction.

If you have multiple cells in a row selected you can only fill up/down.

If you have multiple cells selected in a column you can only fill left/right.

@ben_hensel

Unfortunately I am still at square one.

I am going to highlight the changes I need done to the formula although I cant see if the numbers are highlighted even when I click "preview". Hopefully when I click post I will see that the numbers are highlighted.

formula in chart 1 (see above the attached spreadsheet) would be =COUNTIFS($C$1:$C$19000, 0, $C$2:$C$19000, 0, $C$[COLOR=""]2[/COLOR]:$C$19001, E3) , formula in chart 2 would be =COUNTIFS($C$1:$C$19000, 0, $C$[COLOR=""]3[/COLOR]:$C$1900[COLOR=""]2[/COLOR], H3) , formula in chart 3 would be =COUNTIFS($C$1:$C$19000, 0, $C$[COLOR=""]4[/COLOR]:$C$1900[COLOR=""]3[/COLOR], K3) , formula in chart 4 would be =COUNTIFS($C$1:$C$19000, 0, $C$[COLOR=""]5[/COLOR]:$C$1900[COLOR=""]4[/COLOR], N3) and so on up to formula in chart 100 which would be =COUNTIFS($C$1:$C$19000, 0, $C$[COLOR=""]101[/COLOR]:$C$19[COLOR=""]100[/COLOR], ON3)

Again, can u please tell me if there is a way to drag the formula left and in every third cell in the same row the formula will appear tweaked in the ascending order I highlighted above? This function I would imagine is a function many would need on Excel. I would recommend that Excel add this option into the "series" option within the "fill" function" in the "editing" bar. In other words, make it possible to continue a series of tweaked formulas in a certain direction.

EDIT: I just saw that for some reason only one number is highlighted and the rest are sandwiched between the word COLOR making it hard to see what I need done. So I hope you can understand what it is I am referring to in the formulas

Many thanks,

JackBlack

You can use OFFSET combined with a COLUMN calculation to get the rows to increment as you go across columns.

Put this in F2:

Formula:

Please Login or Register  to view this content.

You can drag it down the column and the only thing which will change will be the E2 at the end (to E3, E4, etc).
The Offset and Column bit gives $C$2:$C$19001.
Copy it into I2 and it will give $C$3:$C$19002 as well as the E2 changing to H2.
Note that this is reliant on your chart columns being 3 apart each time (that's the /3 after the column bit) - if you insert extra columns or delete columns in between, it'll fail.

Hope that does what you want.

Try this formula in F2, then fill across.

Formula:

Please Login or Register  to view this content.

Put some numbers in so we can see the result that you are expecting.

All you will get presently is zero.

Originally Posted by jackblack2

Unfortunately when I copy the formula into I2 the $C$3:$C$19002 part does not change, only the E2 to H2.

The Offset-Column part of the formula won't change when you copy it, but the results it gives will.
(COLUMN()-COLUMN($C1))/3 in column F gives (6-3)/3 which gives 1. In column I it gives (9-3)/3 which gives 2. So in column F, the offset from $C$1 is 1 row, giving $C$2, and in column I the offset is 2, giving $C$3 - which is what you want, I think?

Originally Posted by jackblack2

And when I drag the formula down the chart and paste in the data to sort, the formula gives me the identical result in each cell as in the top cell in the chart.

Without data in column C and in the purple-shaded cells it's difficult to troubleshoot this one - please give us some sample data as well - including the answers you expect to get.

Originally Posted by jackblack2

Also, when I drag the formula left with the + icon function, the formula gets copied into every cell in the row and not just every third cell in the row.

It won't work like this - you'll have to copy it from F3 to I3 to L3, etc. Dragging the formula across all the cells would overwrite your figures in H3, K3, etc. If you're happy to put the H3, K3, etc figures in later, then you can put the formula in F3, leave G3 and H3 blank, select F3:H3 then drag - that way Excel will repeat the 'formula - nothing - nothing - formula - nothing - nothing -' pattern as far as you drag it.

You're welcome. Thanks for the rep and for marking the thread as Solved.