Re: Formula Too Long Issue

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

One last question on this:

So with this formula, what should the formula be so that it will return
blanks instead of displaying it
as 0 (zero)? I tried several ways but couldn't get it to work. Please help
again.

Thank you.



"Toppers" wrote:

Look at HELP in OFFSET function.

To break down my Formula:

(OFFSET(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$26:$N$26,0)-1,24,4)))

Detail_Pivot!$B$27 ==> this positions the start (reference cell) at your
first data cell

0 ==> just positions on the row determined by the
SUMPRODUCT(Detail_Pivot!$A$27:$A$50=$A$1) i.e. the row matching the name

MATCH($B$1,Detail_Pivot!$B$26:$N$26,0)-1==> finds the column corresponding
to the WEEK and we subtract one as its an offset from column B; if the match
is B26, MATCH will return 1 and as our reference cell is B27 we want n offset
of 0 ...hence subtract one.

24 ==> is the number of rows your data occupies (rows 27 to 50)

4 ==>is the number of columns we want to sum

HTH

"mldancing" wrote:

This formula works! Thank you so much HTH. Can you point me to a link where I
can learn more about offset in the formula. I still don't quite get the logic
of it.

Thanks Max for the pointers too!



"Toppers" wrote:

From your original formula I have assumed you want 4 weeks worth of data: if
so, try:

=SUMPRODUCT((Detail_Pivot!$A$27:$A$50=$A$1)*(OFFSET(Detail_Pivot!$B$27,0,MATCH($B$1,Detail_Pivot!$B$26:$N$26,0)-1,24,4)))

where $A$1 has name e.g "Kimberlee Madsen"
$B$1 has first week (of the 4) e.g "Week 10"

You can add error logic as required.

As Max pointed out, you could shorten the formula by using named ranges.

HTH


"Max" wrote:

"mldancing" wrote:
How do you fix it if a formula is too long?

Try making the cell references in the formula shorter
Use short and sweet sheetnames,
like: A, B, C or : 1,2,3 or: T1,T2,T3
Keep all sheets in same book. Avoid using links across books.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
.

Relevant Pages

  • Re: Formula Too Long Issue
    ... Look at HELP in OFFSET function. ... of 0 ...hence subtract one. ... Try making the cell references in the formula shorter ...
    (microsoft.public.excel.misc)
  • Re: Formula Too Long Issue
    ... "mldancing" wrote: ... of 0 ...hence subtract one. ... can learn more about offset in the formula. ... Try making the cell references in the formula shorter ...
    (microsoft.public.excel.misc)
  • Re: Win32 SEH question
    ... .COD file will have the offset as the first bit on each assembler line. ... your offending instruction address, subtract 401000, look in your map file to find the function where that offset would ... >> EIP will show you exactly the offending instruction. ... > to maintain a map file but how do I maintain a list file in Visual C++ 6.0? ...
    (microsoft.public.win32.programmer.kernel)
  • Re: Magic Flight: A Public Key Algorithm Based on a Lossy Commutative Rng Mixer
    ... > merely translates the result to a higher range. ... doesn't really go back to the offset branch or ... >> When in the course of Gaussian Elimination, you subtract the ...
    (sci.crypt)
  • Re: Calc time difference between two date/time/zones
    ... between two date/time/zones as shown below. ... Those are not time zones, ... To subtract those, firstly subtract the date and time treating them as ... then correct by the difference in offset. ...
    (comp.lang.pascal.delphi.misc)