Fiat Coupe Forum
- Founded by Kayjey & James Northam
- Funded by the Club for the benefit of all owners
Fiat Coupe Club UK
join the club
Fiat Coupe Forum
 
» Announced
    Posting images


» Related sites
    Main club site
    fiatcoupe.net


» External data
    owners listed
 
Who's Online Now
3 registered members (Fre3_D0om, FLPete, Jamiepm), 208 guests, and 2 spiders.
Key: Admin, Global Mod, Mod
Forum Statistics
Forums69
Topics113,543
Posts1,340,441
Members1,785
Most Online731
Jan 14th, 2020
Top Posters(All Time)
barnacle 33,522
stan 32,122
Theresa 23,296
PeteP 21,482
bockers 21,070
JimO 17,917
Nigel 17,366
Edinburgh 16,662
RSS Feeds
Club Events
Club Information
Track Events
Rolling Road/RWYB
Social Events
Non-UK Events
Coupé Related Chat
Coupé Spotting
Coupé News/Press
Buying/Selling Advice
Insuring a Coupé
Basic FAQ's
How to Guides
Forum Issues
Technical Problems
General Maintenance
Styling
Tuning
Handling
ICE and Alarm
Coupés for Sale
Coupés Wanted
Parts for Sale
Parts Wanted
Group Buys
Business Forum
Other Vehicles for Sale/Wanted
Other Items for Sale/Wanted
Haggling/Offers
Ebay links
Other Cars
Other Websites
General Chat
Previous Thread
Next Thread
Print Thread
Excel help please. #1631936
24/04/2019 16:01
24/04/2019 16:01
Joined: Jan 2006
Posts: 5,988
Sunny Darlo
Wishy Offline OP
Forum is my life
Wishy  Offline OP
Forum is my life

Joined: Jan 2006
Posts: 5,988
Sunny Darlo
This old chestnut again! As is logical in my head, my second stop (after my google skills have failed me) is to post an Excel question on a car forum.

Here goes.
I want to count how many of 4 cells end in either "R" or "F". In other words, something along the lines of (obviously this syntax doesn't work but you should get the idea) this below

=COUNTIF(RIGHT($D3:$J3), OR("R","F") )

The data that I want to test is in cells D3, F3, H3 and J3 but the cells in-between are either blank or contain dates so I don't mind if the testing is done on the cells D3,F3,H3,J3 or the range D3:J3. Either way would work.

I've come up with the fantastically over-complex looking function below which works but I'm wondering if there is a simpler (or quicker bearing in mind I need to use this formula or a variant of it across 8 columns and 2500 rows of data)

=IF(RIGHT($D3,1)="R",1,0)+IF(RIGHT($F3,1)="R",1,0)+IF(RIGHT($H3,1)="R",1,0)+IF(RIGHT($J3,1)="R",1,0)+IF(RIGHT($D3,1)="F",1,0)+IF(RIGHT($F3,1)="F",1,0)+IF(RIGHT($H3,1)="F",1,0)+IF(RIGHT($J3,1)="F",1,0).

I want this done via a cell function as oppose to a macro, any ideas or is my unwieldy albeit working solution not as bad as it looks?


Up yours Photobucket.
Re: Excel help please. [Re: Wishy] #1631938
24/04/2019 17:20
24/04/2019 17:20
Joined: Mar 2019
Posts: 250
North Macedonia
386ka Offline
Club member 1814
386ka  Offline
Club member 1814
Making a profit

Joined: Mar 2019
Posts: 250
North Macedonia
I don't rely on my excel skills in my daily life, but I wanted to try them out to see if I can help you. laugh

Just tried and tested the following formula.

=COUNTIF($D3:$J3;"*r")+COUNTIF($D3:$J3;"*f")

You can also use COUNTIFS.

The asterisk (*) is used as the wildcard character to match any character, so that's why it's used here. A question mark (?) can be used as a wild card for individual characters, so by using more of them, you can define the fixed length of a word, and its ending.

Try out the formula and see if it works.
PS, sometimes, the integrated help in MS Office helps a lot. wink

Last edited by 386ka; 24/04/2019 17:30.
Re: Excel help please. [Re: Wishy] #1631939
24/04/2019 17:35
24/04/2019 17:35
Joined: Dec 2005
Posts: 8,385
Lightwater, Surrey
DaveG Offline
Club Treasurer Member 311
DaveG  Offline
Club Treasurer Member 311
Je suis un Coupé

Joined: Dec 2005
Posts: 8,385
Lightwater, Surrey
The best I can come up with so far is

=SUM(--(RIGHT(D3:J3,1)="F"))+SUM(--(RIGHT(D3:J3,1)="R"))

But don't press enter after writing out (copying) the formula, press Ctrl+Shift+Enter instead so that the formula is entered as an array formula (Excel adds its own curly brackets like this to denote an array formula)

{=SUM(--(RIGHT(D3:J3,1)="F"))+SUM(--(RIGHT(D3:J3,1)="R"))}

The -- is an Excel trick to force the resulting array of logical values (eg: {FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,TRUE} to be evaluated as numbers.


1996 Portofino 20vt & 2000 Pearl White Plus
1985½ & 2016 2017 Fiat 124 Spider + XF Sportbrake
Re: Excel help please. [Re: 386ka] #1631940
24/04/2019 17:40
24/04/2019 17:40
Joined: Dec 2005
Posts: 8,385
Lightwater, Surrey
DaveG Offline
Club Treasurer Member 311
DaveG  Offline
Club Treasurer Member 311
Je suis un Coupé

Joined: Dec 2005
Posts: 8,385
Lightwater, Surrey
Originally Posted by 386ka

Just tried and tested the following formula.

=COUNTIF($D3:$J3;"*r")+COUNTIF($D3:$J3;"*f")


In the UK we need to replace the ; with , but it works thumb and with less characters than mine and no need to look into array formulae crazy


1996 Portofino 20vt & 2000 Pearl White Plus
1985½ & 2016 2017 Fiat 124 Spider + XF Sportbrake
Re: Excel help please. [Re: DaveG] #1631949
25/04/2019 11:10
25/04/2019 11:10
Joined: Jan 2006
Posts: 5,988
Sunny Darlo
Wishy Offline OP
Forum is my life
Wishy  Offline OP
Forum is my life

Joined: Jan 2006
Posts: 5,988
Sunny Darlo
The winner is
Code
=COUNTIF($D3:$J3,"*r")+COUNTIF($D3:$J3,"*f")
or alternatively
Code
=SUM(COUNTIF($D3:$J3,{"*r","*f"}))


I'd never previously realised that Excel handled wild cards. It's funny that you mention using the in built help, it made me realise that I never even look at it these days (except if I'm using a totally new software package to me) and tend to head straight for Google and the likes of stack exchange for answers. I've been using Excel for long enough for the only option for help to have been the in built help when I first started which was probably Windows 3.1 era.

At some point I intend to get my head into when you need to use array formulae. I've used them a quite a few times but only ever in response to instructions that told me I needed to and never really understood.

Anyway, thank you both. smile


Last edited by Wishy; 25/04/2019 11:11.

Up yours Photobucket.

Powered by UBB.threads™ PHP Forum Software 7.7.1
(Release build 20190129)
PHP: 7.3.33 Page Time: 0.013s Queries: 15 (0.006s) Memory: 0.7665 MB (Peak: 0.8325 MB) Data Comp: Off Server Time: 2024-03-29 08:33:25 UTC