complicated formula

[Jonathan Cooper]

A while back, I posted a question about a formula (see below). I did receive an answer and I've integrated it into my worksheet. The problem is, there are 25,000 rows in this data table, and there are four formulas for every row, so about 100,000 cells with this formula in it. So it takes about 10 minutes for Excel to recalculate this information. There has got to be an easier way. Please read previous post below

Also, I'm thinking that DGET might work, if I could figure out how to structure the argument

formula
=INDEX(mapping!G$5:G$1006,SUMPRODUCT(MAX((mapping!$A$1:$A$1006<=$C5)*(mapping!$B$1:$B$1006>=$C5)*(mapping!$C$1:$C$1006<=$D5)*(mapping!$D$1:$D$1006>=$D5)*(mapping!$E$1:$E$1006<=$A5)*(mapping!$F$1:$F$1006>=$A5)*ROW(mapping!$A$1:$A$1006)-4))

Previous Post

----- Jonathan Cooper wrote: ----

This will work, but I was hoping for something shorter

I'd also like the formula to return an error if more than one match exists

----- Frank Kabel wrote: ----

H
try the following formul
=INDEX(G1:G1000,SUMPRODUCT(MAX(('sheet1'!A1:A100<=A1)*('sheet1'!B1:B10

=A1)*('sheet1'!C1:C100<=B1)*('sheet1'!D1100>=B1)*('sheet1'!E1:E100<

C1)*('sheet1'!F1:F100>=C1)*ROW('sheet1'!A1:A100)))

-
Regard
Frank Kabe
Frankfurt, German

I'm looking to write some type of lookup formula (match, offset

etc...) that will check several variable rangesB), sub account range (column C and D) and department range (column
and F), then I want to know the text in column G

find the row where 50000 is >= column A, and <=column B an
find the row where 300 is >= column C, and <=column D an
find the row where 50 is >= column E, and <=column

to lookup the text value in column G of the row that meets thes
criteria. I don't think sumproduct will work, because the answer I'
looking for is text

 

[Frank Kabel]

Hi Jonathan
DGETwould probably also similar slow. If you really need
100000 cells with this formula this WILL slow down Excel.
you may have a look if pivot tables could help you getting
your report (as I'm not sure why you need so many
formulas).
If pivot tables won't work for you you may consider using
a database for this (not sure about your underlying data
though). but if you need a formula approach I don't think
there will be a fast way
Just turn of automatic calculation and drink a cup of
coffee while recalculating

-----Original Message-----
A while back, I posted a question about a formula (see

below). I did receive an answer and I've integrated it
into my worksheet. The problem is, there are 25,000 rows
in this data table, and there are four formulas for every
row, so about 100,000 cells with this formula in it. So
it takes about 10 minutes for Excel to recalculate this
information. There has got to be an easier way. Please
read previous post below:

Also, I'm thinking that DGET might work, if I could

figure out how to structure the argument.

formula:
=INDEX(mapping!G$5:G$1006,SUMPRODUCT(MAX((mapping!

$A$1:$A$1006<=$C5)*(mapping!$B$1:$B$1006>=$C5)*(mapping!
$C$1:$C$1006<=$D5)*(mapping!$D$1:$D$1006>=$D5)*(mapping!

 

[Frank Kabel]

Hi
run a small test for DGET (and for my design it was
slower, approx 15%)

For using DGET you first have to set-up a criteria range
with your conditions. e.g. if your data has the structure
(lets say in col. A:C)
item - number - value
....
....

yo could set-up a lookup criteria like (in D1:F2)
item - number - value
item1 - 10

Now use DGET as follows:
=DGET(A1:C100,3,D1:F2)
to get the value for item1 with the number 10

-----Original Message-----
Let me explain why I do this.

I am in effect, creating a Profit & Loss statement in

Excel. I bring in all the data from our general ledger,
cross reference each entry against my mapping table to
determine which section of the P&L it should go to (e.g.,
Cost of Good Sold - Material). Then I run a pivot table
over the data (including the result of the big long
formula) so that the pivot table can add up/sort/group
everything that is supposed to go into 'Cost of Goods
Sold - Material'. It's really very cool. I used to do
this with vlookup formulas but that meant I was constantly
maintaining the thing because every possible combination
had to be mapped, otherwise the lookup wouldn't be able to
find an exact match.

I decided to be a little smarter about it and use the

ranges that you helped me with earlier. It's just taking
forever to update/maintain. I've read the example for
DGET several times and I still can't figure out how to
structure the formula. I'd like to try it to see if the
speed is any better.