Creating multiple IF lines - complicated problem -

L

lokideviluk

Hi,

I've used excel in its simplist form for a while now, however just
recently I've attempted to make something clearly beyond my skill level
:)

Ive displayed below a flowsheet of what Im trying to achieve

'[image: http://images6.theimagehosting.com/Excelsheet.JPG]'
(http://www.theimagehosting.com)

Currently the below is what I've done, This takes into account only the
first instruction excluding the part where it verifies if the sum = a
specific amount and if its less, it replaces with the original amount

=IF(F17<L11,F17*K11, "=IF(AND(f17>751,f17<100),f17*.32")

I would welcome any help, and would be extremely thankful.

O, all of this I am hoping to be able to have calculated in one cell if
possibly.

Regards
Ryan
 
P

Pete_UK

I would suggest that you create a small table somewhere which contains
your cut-offs, factors and comparison values, as follows:

0 0.36 217.80
751 0.32 275.20
1001 0.30 332.65

and so on for your seven conditions. I'm not sure if your first value
should be 100, as this is shown in your formula. Suppose this table is
located from X1 to Z7 on the same sheet. I imagine that F17 contains
the number to be acted upon, so try this formula:

=MAX(F17*VLOOKUP(F17,X$1:Z$7,2,1),VLOOKUP(F17,X$1:Z$7,3,1))

If the first value in your table is 100, you don't specify what you
want to do if F17 is less than this.

The formula can be copied down if you have other values in column F.

Hope this helps.

Pete
 
G

green.a.4

Yo, the correct way to write the formula is this:
=IF(F17<L11,F17*K11,IF(AND(F17>751,F17<100),F17*0.32,"")
the 'if' statement works like this: IF(condition,if true,if false)

I would however like to know what value in f17 you are expecting to be
greater than 751 AND less than 100 at the same time?!

Perhaps you need to use the'OR' function?

hope this helps a little.
 
L

lokideviluk

Hi guys,

I've tried your method Pete_uk and it seems to working perfectly,
need to add in the other figures but its just a case of duplication.

I apologise about the 100 thing, it should be 1000

The varibles are

Times by Range Minimum
0.36 <750 217.8
0.32 751-1000 275.2
0.3 1001-1500 332.65
0.28 1501-2000 455.5
0.27 2001-3000 564.99
0.25 3001-4000 788.77
0.23 4001> 961.98

Regards
Rya
 
P

Pete_UK

Thanks for feeding back. Your flowchart only showed 3 sets of
variables, but at least you seem to have grasped how the method works.

Pete
 
Top