Saturday, March 26, 2011

yogi_Computation Involving Multiple And Or Conditions


Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

question is ...
my conditions sound like this:
IF D2 is >0 or D2 is <=100, then value is 0-100
IF D2 is >100 or D2 is <=500, then value is 100-500
IF D2 is >500 or D2 is <=1000, then value is 500-1000
IF D2 is >1000 or D2 is <=10000, then value is 1000-10000
IF D2 is >10000 or D2 is <=1000000, then value is 10000-1000000
----------------------------------------------------------------------------------

In the following solution, I have used the VLOOKUP function by setting up a table in cells A1:B7 ... when the LookUp value is entered in cell D2, the value is LookedUp in column A and the result from column B is put in cell E2.



The table in cells A1:B7 can be moved to another location in the same sheet or even to another sheet of the same spreadsheet.