Excel formula help - Corsa Sport - for Vauxhall and Opel Corsa B, Corsa C and Corsa D

Not logged in [Login - Register]

You Are Not Registered Or Not Logged In

Corsa Sport » Message Board » Off Day » Geek Day » Excel formula help

New Topic

New Poll

Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.

Brett

Premium Member

Registered: 16th Dec 02
Location: Manchester
User status: Offline

7th May 14 at 14:39

Is there a way to achieve the above without the use of the "difference" helper column?

Basically a count on the difference between the A and B columns.

Thanks

Dom

Member

Registered: 13th Sep 03
User status: Offline

7th May 14 at 15:17

Can you use VBA? Makes it a piece of piss if you can

Brett

Premium Member

Registered: 16th Dec 02
Location: Manchester
User status: Offline

7th May 14 at 15:19

Na, I don't want to do anything like that lol

Is it possible with formula? I'm sure it should be easy but nothing works ffs

RichR

Premium Member

Registered: 17th Oct 01
Location: Waterhouses, Staffordshire
User status: Offline

7th May 14 at 16:49

Why can't you keep the difference column?

Brett

Premium Member

Registered: 16th Dec 02
Location: Manchester
User status: Offline

7th May 14 at 17:34

That's no concern of yours

Dom

Member

Registered: 13th Sep 03
User status: Offline

7th May 14 at 17:38

quote:
Originally posted by Brett
Na, I don't want to do anything like that lol

Using VBA/Macro is the only way i can think of looping through rows. Alternatively, can you not hide the difference column on another (locked) sheet?

Brett

Premium Member

Registered: 16th Dec 02
Location: Manchester
User status: Offline

7th May 14 at 17:40

It's cool, I'll suss it out, fairly certain it can be done. Cheers though, Dom.

evilrob

Premium Member

Registered: 16th Mar 12
Location: Your mum's house
User status: Offline

7th May 14 at 17:43

How many rows are we talking here?

Brett

Premium Member

Registered: 16th Dec 02
Location: Manchester
User status: Offline

7th May 14 at 17:46

Too many to be using direct cell references if that's what you mean, plus it's a growing list.

Gaz

Member

Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline

7th May 14 at 18:39

You need a difference column of some sort to query against. Alternatively a SUMPRODUCT formula may help off the top of my head.

[Edited on 07-05-2014 by Gaz]

Brett

Premium Member

Registered: 16th Dec 02
Location: Manchester
User status: Offline

7th May 14 at 18:45

How come you deleted the post about knocking up a spreadsheet?

I'm waiting eagerly here

EDIT: Oh you've changed your tune now lol it was 'easy to do' 5mins ago. I guess the attempt at doing the spreadsheet failed lol Cheers anyway

[Edited on 07-05-2014 by Brett]

Gaz

Member

Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline

7th May 14 at 18:47

Haha! Sorry mate will whip one up shortly. It wasn't as easy as I first thought. (My lads bed time first though)

Is this purely limited to two colums with no flexibility to use a 3rd?

Brett

Premium Member

Registered: 16th Dec 02
Location: Manchester
User status: Offline

7th May 14 at 18:50

If a third could be used it's piss easy. I'm setting a challenge here

Dom

Member

Registered: 13th Sep 03
User status: Offline

7th May 14 at 19:44

Brett

Premium Member

Registered: 16th Dec 02
Location: Manchester
User status: Offline

7th May 14 at 20:29

OMG, so simple! I told you lot! Complete fools, the lot of you

=SUMPRODUCT(--(B:B-A:A=400))

Gaz

Member

Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline

7th May 14 at 20:33

ballache!

=SUM(IF(B2-A2=400,1,)+IF(B3-A3=400,1,)+IF(B4-A4=400,1,)+IF(B5-A5=400,1,))

Gaz

Member

Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline

7th May 14 at 20:34

quote:
Originally posted by Brett
OMG, so simple! I told you lot! Complete fools, the lot of you

=SUMPRODUCT(--(B:B-A:A=400))

Brett

Premium Member

Registered: 16th Dec 02
Location: Manchester
User status: Offline

7th May 14 at 20:36

quote:
Originally posted by Gaz
ballache!

=SUM(IF(B2-A2=400,1,)+IF(B3-A3=400,1,)+IF(B4-A4=400,1,)+IF(B5-A5=400,1,))

Mate, you ignored the bit about direct cell references too. Hope you didn't spend too long on that

Thanks for you time tho

Gaz

Member

Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline

7th May 14 at 20:38

quote:
Originally posted by Brett

quote:
Originally posted by Gaz
ballache!

=SUM(IF(B2-A2=400,1,)+IF(B3-A3=400,1,)+IF(B4-A4=400,1,)+IF(B5-A5=400,1,))

Mate, you ignored the bit about direct cell references too. Hope you didn't spend too long on that Thanks for you time tho

double

Although, your formula you suggested returns a #value! error when I put it in to the spreadsheet I set up.

Gaz

Member

Registered: 24th Aug 03
Location: Widnes, Cheshire
User status: Offline

7th May 14 at 20:39

ah, because I had headers to the row...
you learn something new everyday

Brett

Premium Member

Registered: 16th Dec 02
Location: Manchester
User status: Offline

7th May 14 at 20:53

You were on the right track with your sumproduct comment earlier tbf

Brett