asked on

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

ASKER CERTIFIED SOLUTION

terencino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

terencino

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

SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

terencino

Here is the update to include ">="
AMALGAMATION.xlsx

SOLUTION

Simon France

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cpatte7372

ASKER

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

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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)

terencino

or in D1 use =COUNTIF(D3:D142,"V")

cpatte7372

ASKER

Experts,

Thanks it worked like a dream.

I'm not sure who to assign the points to???

terencino

Just feel free to split appropriately to all those who contributed to your solution Carlton, we're all happy to help

cpatte7372

ASKER

Thank you all very much.

Appreciate it.

Cheers