Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / July 2006

Tip: Looking for answers? Try searching our database.

Creating multiple IF lines - complicated problem -

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
lokideviluk - 11 Jul 2006 21:18 GMT
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

Signature

lokideviluk

Pete_UK - 12 Jul 2006 01:52 GMT
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

> Hi,
>
[quoted text clipped - 26 lines]
> lokideviluk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36249
> View this thread: http://www.excelforum.com/showthread.php?threadid=560445
green.a.4@pg.com - 12 Jul 2006 12:59 GMT
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.

> Hi,
>
[quoted text clipped - 26 lines]
> lokideviluk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36249
> View this thread: http://www.excelforum.com/showthread.php?threadid=560445
lokideviluk - 12 Jul 2006 14:24 GMT
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
Pete_UK - 12 Jul 2006 15:02 GMT
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

> Hi guys,
>
[quoted text clipped - 22 lines]
> lokideviluk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36249
> View this thread: http://www.excelforum.com/showthread.php?threadid=560445
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.