Posts by Barrie Davidson

Try something like this (and just change the string you are matching):

=SUMPRODUCT((OFFSET(Database,0,4,,1)="Barrie")+0,OFFSET(Database,0,6,,1))

Best regards,

Quote

Originally posted by DNAgirl
Hi All,

I think I need a nested SUMIF but I'm not sure about how to do it. I need to sum based on 2 conditions:

plate, score and status are my column headers. I need to sum if my status is "good" and my plate name is "x".

Any thoughts?

Thanks,

Jen

Display More

Have you tried SUMPRODUCT? Something like:

=SUMPRODUCT(A1:A100,(B1:B100="good")+0,(C1:C100="x")+0)

Regards,

Use an IF statement to return the NA value, charts will not put this on the chart. Something like:

=IF(your formula=0,NA(),your formula)

Regards,

Is it viable to put a table of the peoples' names on another worksheet (for this example "Sheet2")? If you can do this, try this formula (assumes your lookup table of names is in "Sheet2" in A1:A20):

=SUMPRODUCT((ISNUMBER(MATCH(B9:B24,Sheet2!$A$1:$A$20,0)))+0,C9:
C24)

Regards,

Quote

Originally posted by yyama8
Hi Ralph and Barrie,

Thank you for your help. It works, however, your ways will work only to a specific range. I could be wrong. If you could show me how to autofill from the above cell to the next cell down without specifying which set of ranges.

Thanks,
Yuki

This will autofill from the first non-empty cell above the activecell to the first non-empty cell below the activecell:

Dim TopRow, BottomRow As Long
TopRow = ActiveCell.End(xlUp).Row
BottomRow = ActiveCell.End(xlDown).Row - 1
Range(Cells(TopRow, ActiveCell.Column), _
Cells(BottomRow, ActiveCell.Column)). _
DataSeries Type:=xlChronological, Date:= _
xlDay, Step:=1

Hope this helps.

Try something like this:

Range("A1:A10").DataSeries Type:=xlChronological, Date:= _
xlDay, Step:=1

The quickest way is to use the GOTO hotkey (F5) and then type in the cell address.

Does this help you out?

Gotta luv the tough problems ;;)

Try this attachment.

Okay, 3rd time's the charm?
(knew this problem wouldn't be easy...those are the best kind!)

It's displaying that dash because the opening gauge number is less than the closing gauge number. I've changed the formulas in J3 and L3 in the attachment to correct problems (also found a problem if the closing gauge inches was less than the opening gauge).

Hope I've got it this time!

Quote

Originally posted by Dave Hawley
Hi cadder

I see barry has beaten me to it :))

Not too often that happens!!

Here I am basking in my victory :bouncing:

Try this:

=IF(OR(M17="No data",N18="No data"),"Missing data",M17-N18)

Regards,

You can email me the spreadsheet and I'll see if I can help you out.

To bring in the quotation mark closer to your fraction, replace the three question marks (???) with three number symbols (###), there's two of them in the formula.

To get rid of the dash (-), use this formula:

=SUBSTITUTE(A1,"-","")

Regards,

Not sure why the formula isn't making it to my post. Have a look at the attachment.

Regards,

I got the values you were looking for. Can you post your spreadsheet and I'll take a look at it?

Thanks Jack!

:wink2:

Try this in J3:

=LEFT(I3,FIND("'",I3)-1)-LEFT(H3,FIND("'",H3)-1)&"'"&TEXT(SUBSTITUTE(MID(I3,FIND("'",I3)+1,LEN(I3)-FIND("'",I3)),"""","")-SUBSTITUTE(MID(H3,FIND("'",H3)+1,LEN(H3)-FIND("'",H3)),"""","")," 0 ???/???")&""""

and this in L3:

=K3*(LEFT(I3,FIND("'",I3)-1)-LEFT(H3,FIND("'",H3)-1)+(SUBSTITUTE(MID(I3,FIND("'",I3)+1,LEN(I3)-FIND("'",I3)),"""","")-SUBSTITUTE(MID(H3,FIND("'",H3)+1,LEN(H3)-FIND("'",H3)),"""",""))/12)

Hope this is what you need.

Regards,

You can do this via formulas. Use these formulas:

A3=INDIRECT("A"&IF(ISERROR(MATCH(A4,$A$9:$A$183,0)),MATCH(A4,$A$9:$A$183)+8,MATCH(A4,$A$9:$A$183,0)+8))

B3=INDIRECT("D"&IF(ISERROR(MATCH(A4,$A$9:$A$183,0)),MATCH(A4,$A$9:$A$183)+8,MATCH(A4,$A$9:$A$183,0)+8))

A5=INDIRECT("A"&IF(ISERROR(MATCH(A4,$A$9:$A$183,0)),MATCH(A4,$A$9:$A$183)+9,MATCH(A4,$A$9:$A$183,0)+8))

B5=INDIRECT("D"&IF(ISERROR(MATCH(A4,$A$9:$A$183,0)),MATCH(A4,$A$9:$A$183)+9,MATCH(A4,$A$9:$A$183,0)+8))

B4=IF(A3=A5,B3,(((A5-A4)/(A5-A3))*B3)+(((A4-A3)/(A5-A3))*B5))

Hope this helps you out.

Regards,

Do you actually need ' in front of the number or could you format the cell as a text? You could convert the numbers to text really quickly by: Selecting your cells. From the main menu, select Data|TextToColumns. Select Delimited Data type, and click next twice. Then select Text as the Column Data Format and then click Finish. Your numbers are now text values.