Help for a gumbie!

Bakayaro

New Member
Joined
Aug 8, 2011
Messages
4
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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?
 
Upvote 0
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 -->
 
Upvote 0
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),""))
 
Upvote 0
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
 
Upvote 0
I don't know if Rick is online ATM so try
Code:
=IF(C1="US",IF(G1<400,0,110),IF(C1="NQ",IF(G1<400,0,165),0))
 
Upvote 0
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))
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,566
Messages
6,179,551
Members
452,927
Latest member
rows and columns

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top