Help for a gumbie!

[Bakayaro]

Hi all,

Sorry to bother you but I'm relatively new to Excel and I'm ripping my hair out trying to work out one of these bloody nested if function thingies.

Here's the drama; in column C I have several different entries, two of which are NQ and US. What I need to do is if in column C it says "US" or AND the amount in column H is greater or equal to 400 then I need column H to show 110. If it is under 400 it must show 0. But if in column C it says "NQ" and the amount in column H is greater or equal to 400 then I need column H to show 165.

Does this make any sense?

I appreciate any assistance you may be able to give me.

 

[Rick Rothstein]

What should the formula return if Column C is "NQ" but Column H is less than 400?

Also, what should the formula return if Column C is neither "NQ" nor "US"? How many other possibilities for Column C are there?

 

[Bakayaro]

Thanks, Rick. Sorry mate, I made a bit of a typo. It should have read;

In column C I have several different entries, two of which are NQ and US. What I need to do is if in column C it says "US" or AND the amount in column G is greater or equal to 400 then I need column H to show 110. If it is under 400 it must show 0. But if in column C it says "NQ" and the amount in column G is greater or equal to 400 then I need column H to show 165.

If Column C is "NQ" but Column G is less than 400 the the cell in H (same row) can be left blank.

If Column C is neither "NQ" nor "US" the formula can just leave the cell in H blank. There are 3 possibilities for Column C including US and NQ.

Thanks again, Rick. You're a legend.

Kind regards,

Scott
<!-- / message -->

 

[Rick Rothstein]

I think this formula placed in H1 and copied down as needed will do what you want....

=IF(C1="US",IF(G1<400,0,110),IF(C1="NQ",IF(G1<400,"",165),""))

 

[Bakayaro]

I know you're in Jersey, Rick but if you were anywhere near Brisbane, Australia I'd be buying you a few beers old son.
It works a treat. One small problem I've got is that I shouldn't have left any cells blank. It stuffs up a few other calculations running. So instead of blank cells, what would I have to do to have 0 put there instead?
Thanks for all of your help, mate.

Regards,

Scott

 

[Michael M]

I don't know if Rick is online ATM so try

=IF(C1="US",IF(G1<400,0,110),IF(C1="NQ",IF(G1<400,0,165),0))

 

[Rick Rothstein]

So instead of blank cells, what would I have to do to have 0 put there instead?

All you have to do is change the "" (which is what "displays" the empty cells) to 0...

=IF(C1="US",IF(G1<400,0,110),IF(C1="NQ",IF(G1<400,0,165),0))

 

[Rick Rothstein]

All you have to do is change the "" (which is what "displays" the empty cells) to 0...

=IF(C1="US",IF(G1<400,0,110),IF(C1="NQ",IF(G1<400,0,165),0))

Actually, since all you want are zeroes rather than zeroes and empty cells for the non 110/165 conditions, I think this simpler formulas will do the same thing as what I posted a few minutes ago...

=IF(G1>=400,IF(C1="US",110,IF(C1="NQ",165,0)),0)

 

[Michael M]

Sorry Rick, didn't mean to jump you....wasn't sure whether you were still aboard, and Bakayaro is in my part of the world.

 

[Bakayaro]

Gentlemen,

I can't thank you enough for your assistance. You are both worth your weight in gold in helping out a complete stranger.

Now that I have to use Excel daily, I can only hope to become as proficient as you two. I've bought a copy of Excel for stupid bastards so once it arrives I'll try not to be a pest on this forum so often.

All the best,

Scott