Excel Conditional Formula Request

Hello Experts,

Can someone please assist me in writing a formula that will do the following:

In cell G3 if cell D3 is > 4 but less than 20 then insert the following character v
In cell H3 if cell D3 is >21 but less than 50 then insert the following character v
In cell I3 if cell D3 is > 51 insert the following character v
In cell J3 if cell D3 is greater than 1 but less than 4 then insert the following character v


Your help will greatly appreciated.

Regards

Carlton
AMALGAMATION.xlsx
cpatte7372Asked:
Who is Participating?
 
terencinoConnect With a Mentor Commented:
Does this work for you Carlton?
...Terry
AMALGAMATION.xlsx
0
 
terencinoCommented:
You might want to consider using "greater than or equal to" expressions to cover the ones that fall through the gaps. Can easily update the formulas to suit
...Terry
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello

>> In cell G3 if cell D3 is > 4 but less than 20 then insert the following character v
=IF(AND(D3>4,D3<20),"v","")

>>In cell H3 if cell D3 is >21 but less than 50 then insert the following character v
=IF(AND(D3>21,D3<50),"v","")

>>In cell I3 if cell D3 is > 51 insert the following character v
=IF(D3>51,"v","")

>>In cell J3 if cell D3 is greater than 1 but less than 4 then insert the following character v
=IF(AND(D3>1,D3<4),"v","")

cheers, teylyn
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
terencinoCommented:
Here is the update to include ">="
AMALGAMATION.xlsx
0
 
SimonFranceConnect With a Mentor Commented:
In G3 put =IF(D3>=4,IF(D3<=20,"V",""),"")
In H3 put =IF(D3>=21,IF(D3<=50,"V",""),"")
In I3 put =IF(D3>=51,"V","")
In J3 put =IF(D3>=1,IF(D3<=4,"V",""),"")

The attached has got the formulas included (you'll just need to fill down).
amalgamation.xlsx
0
 
cpatte7372Author Commented:
Thanks experts,

Is it possible to make excel recognise a character as a number? For example, the values in the formulas you suggested will be 'V'. I would Excel to recognise 'V' as the value number 1, so that I can total the 'V's.

I hope that makes sense
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You can use Countif() to count the "v"s

=COUNTIF(G:G,"v")

Or replace the "v" with a 1, then you can sum the results. So, instead of...

=IF(AND(D3>4,D3<20),"v","")

... use

=IF(AND(D3>4,D3<20),1,0)
0
 
terencinoCommented:
or in D1 use =COUNTIF(D3:D142,"V")
0
 
cpatte7372Author Commented:
Experts,

Thanks it worked like a dream.

I'm not sure who to assign the points to???
0
 
terencinoCommented:
Just feel free to split appropriately to all those who contributed to your solution Carlton, we're all happy to help
0
 
cpatte7372Author Commented:
Thank you all very much.

Appreciate it.

Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.