Excel Query

Cotta

Hi All,

I have volumes in column "J" and I want to input a formula into column "K" to define if the value is less than 100, between 100 and 500 or if it's greater than 500. Is this possible and if so how?

Thanks

InsideInsurance

Yes, its a simple nested IF statement http://www.techonthenet.com/excel/formulas/if_nested.php

Cotta

InsideInsurance wrote: »

Yes, its a simple nested IF statement http://www.techonthenet.com/excel/formulas/if_nested.php

Tried the following but it keeps returning an error.

=IF([@GrossPremiumGBP]<10,"Under 10" IF([@GrossPremiumGBP]>10<=500, "Between 10 and 500"" IF([@GrossPremiumGBP] >500, "Greater")))

Bigphil1474

Cotta - you need to start the other way round. Try :-


=IF(A2>500,"Greater",IF(A2>10,"Between 10 and 500",IF(A2>0,"Under 10")))


Replaced your grosspremium cell ref with A2, but if you start at >500 = Greater, then between, then under 10, should work. Basically saying if it s greater than 500 then that's it, if its not, it goes to the next IF and so on. Tested and worked for me, just need to reference the right cell.

paddyrg

Even more fun, you can use conditional formatting in column J to colour the cells for the different value ranges :-) I thought it was a gimmick until I started using it - then use it loads, it's really useful!

matttye

=IF([&#64;GrossPremiumGBP]<10,"Under 10",IF(AND([&#64;GrossPremiumGBP]>=10,[&#64;GrossPremiumGBP]<=500), "Between 10 and 500",IF([&#64;GrossPremiumGBP] >500, "Greater")))

Just fixing the code in post #3.

GRM

Less typing is always good

=IF([@GrossPremiumGBP]<10,"Under 10",IF([@GrossPremiumGBP]>=500,"Over 500","Between"))