Complicated IF statement - desperately need some help

Afternoon everyone.

Have been struggling to get a formula together for the following. I am being 
told due to its complexity that I will probably need to break it down into 2 
sections but hoping someone can help me simplify things.

I need to do the following

A1 = 1 (although this can be 1,2,3,4,5) (This is my variable I am struggling 
with)
B1 = €10000 (TARGET)
C1 - €6500 (ACTUAL)

Then I have a few boxes showing the following

                    <70%             between 70-99%      >100%
A5 = 1         B5 = 10%         C5 = 15%              D5 = 18%
A6 = 2         B6 = 12%         C6 = 18%              D6 = 21%
A7 = 3         B7 = 14%         C7 = 21%              D7 = 24%
A8 = 4         B8 = 16%         C8 = 24%              D8 = 27%
A9 = 5         B9 = 18%         C9 = 27%              D9 = 30%

So potentially there are 3 statements 
1 showing under 70% 
1 greater than 70% but less than 99%
1 greater than 100%

=IF(SUM(C1/B1)<70%,C1*B5  
=IF(AND(SUM(C1/B1)>70%<90%)),C1*C5 (I know this is wrong)

My problem is I now need to add the variable which is in A1 as it determines 
the % applicable in the boxes above. 

Apologies if this is confusing but hopefully I have explained it correctly

Thanks in advance for any help.



0
Utf
3/24/2010 3:03:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
664 Views

Similar Articles

[PageSpeed] 11

Look again in the help index for AND and you do not need to use sum(unless 
summing, of course)
SUM(C1/B1)<70

(C1/B1)<70
or
C1/B1<70
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Mark D" <MarkD@discussions.microsoft.com> wrote in message 
news:8843E75D-0727-47AB-A964-C1D9E10A23B2@microsoft.com...
> Afternoon everyone.
>
> Have been struggling to get a formula together for the following. I am 
> being
> told due to its complexity that I will probably need to break it down into 
> 2
> sections but hoping someone can help me simplify things.
>
> I need to do the following
>
> A1 = 1 (although this can be 1,2,3,4,5) (This is my variable I am 
> struggling
> with)
> B1 = €10000 (TARGET)
> C1 - €6500 (ACTUAL)
>
> Then I have a few boxes showing the following
>
>                    <70%             between 70-99%      >100%
> A5 = 1         B5 = 10%         C5 = 15%              D5 = 18%
> A6 = 2         B6 = 12%         C6 = 18%              D6 = 21%
> A7 = 3         B7 = 14%         C7 = 21%              D7 = 24%
> A8 = 4         B8 = 16%         C8 = 24%              D8 = 27%
> A9 = 5         B9 = 18%         C9 = 27%              D9 = 30%
>
> So potentially there are 3 statements
> 1 showing under 70%
> 1 greater than 70% but less than 99%
> 1 greater than 100%
>
> =IF(SUM(C1/B1)<70%,C1*B5
> =IF(AND(SUM(C1/B1)>70%<90%)),C1*C5 (I know this is wrong)
>
> My problem is I now need to add the variable which is in A1 as it 
> determines
> the % applicable in the boxes above.
>
> Apologies if this is confusing but hopefully I have explained it correctly
>
> Thanks in advance for any help.
>
>
> 

0
Don
3/24/2010 3:09:14 PM
How about something like this?


1	10000	6500

	< 70%	> 70% < 99%	> 100%
	0%	70%	10000%
1	10%	15%	18%
2	12%	18%	21%
3	14%	21%	24%
4	16%	24%	27%
5	18%	27%	30%





	650		10%

	~=HLOOKUP(C1/B1,B4:D9,A1+1,TRUE)*C1




0
Ziggy
3/25/2010 8:46:46 PM
On Mar 25, 2:46=A0pm, Ziggy <ziggy...@xmission.com> wrote:
> How about something like this?
>
> 1 =A0 =A0 =A0 10000 =A0 6500
>
> =A0 =A0 =A0 =A0 < 70% =A0 =A0 =A0 =A0> 70% < 99% =A0 =A0 =A0 > 100%
> =A0 =A0 =A0 =A0 0% =A0 =A0 =A070% =A0 =A0 10000%
> 1 =A0 =A0 =A0 10% =A0 =A0 15% =A0 =A0 18%
> 2 =A0 =A0 =A0 12% =A0 =A0 18% =A0 =A0 21%
> 3 =A0 =A0 =A0 14% =A0 =A0 21% =A0 =A0 24%
> 4 =A0 =A0 =A0 16% =A0 =A0 24% =A0 =A0 27%
> 5 =A0 =A0 =A0 18% =A0 =A0 27% =A0 =A0 30%
>
> =A0 =A0 =A0 =A0 650 =A0 =A0 =A0 =A0 =A0 =A0 10%
>
> =A0 =A0 =A0 =A0 ~=3DHLOOKUP(C1/B1,B4:D9,A1+1,TRUE)*C1

Oops, that 10000% should now be 100%. I played with the formula and
didn't change that.
0
Ziggy
3/25/2010 9:28:03 PM
On Mar 25, 3:28=A0pm, Ziggy <ziggy...@xmission.com> wrote:
> On Mar 25, 2:46=A0pm, Ziggy <ziggy...@xmission.com> wrote:
>
> > How about something like this?
>
> > 1 =A0 =A0 =A0 10000 =A0 6500
>
> > =A0 =A0 =A0 =A0 < 70% =A0 =A0 =A0 =A0> 70% < 99% =A0 =A0 =A0 > 100%
> > =A0 =A0 =A0 =A0 0% =A0 =A0 =A070% =A0 =A0 10000%
> > 1 =A0 =A0 =A0 10% =A0 =A0 15% =A0 =A0 18%
> > 2 =A0 =A0 =A0 12% =A0 =A0 18% =A0 =A0 21%
> > 3 =A0 =A0 =A0 14% =A0 =A0 21% =A0 =A0 24%
> > 4 =A0 =A0 =A0 16% =A0 =A0 24% =A0 =A0 27%
> > 5 =A0 =A0 =A0 18% =A0 =A0 27% =A0 =A0 30%
>
> > =A0 =A0 =A0 =A0 650 =A0 =A0 =A0 =A0 =A0 =A0 10%
>
> > =A0 =A0 =A0 =A0 ~=3DHLOOKUP(C1/B1,B4:D9,A1+1,TRUE)*C1
>
> Oops, that 10000% should now be 100%. I played with the formula and
> didn't change that.

This leaves the A1 as a manual input.

If you're asking what I think you're asking, you're asking about a
circular reference.  You want the formula to pick A1 but the formula
is dependent on the value in A1.

Or? What determines A1?
0
Ziggy
3/25/2010 9:34:56 PM
Reply:

Similar Artilces:

Exchange 2000 box freezes and needs reboot DAILY!
Hello, I'm having a problem with my mail server. The box freezes (Move mouse NOTHING hit keyboad NOTHING) and seems to shut it's self down (At the time it hung). Fun Eh? My first thoughts were screen saver settings. The next thing was the video card drivers, changed them and same thing. So tried new vid card to no avail. Changed to PCI vid card still the same effect. Now starting to think it's the motherboard or some combination of settings. Does anyone have any idea of what may be causing my problem? Looking into the event viewer there is nothing extraordinary about the...

Conditional formatting help
Hi, I can't figure out the way I should create this conditional formatting. It's probably very simple though. If a cell, let's say J5, is empty for three days, then the background of cell A5 turns red. Any help would be appreciated Thanks Any 72 consecutive hours? Starting when? What if someone entered something then deleted it an hour later? Would that re-start the three day period? You would need some type of event code to watch J5 to reset the counter in that case. I don't think it is "probably very simple" but I may be over-reading this or you have ...

Help with CTabCtrl
Anyone got a link (or something) to some code which shows how to use a CTabCtrl? In essence I'm trying to nest CPropertyPages -- a CPropertySheet with a number of pages, each with a number of pages below it. For some reason I just can't get my mind around how to do this. The "right" way seems to be: CPropertySheet CPropertyPage some displayed data and some CTabCtrls... and so on. But I just can't get how to insert a control into the CTabCtrl... Thanks for any help. "Frank Hickman" wrote > Take a look at the attached sample...it'll...

No password needed to access email? How do I become more secure?
New to Microsoft Outlook. I'm a previous Outlook Express user, and was able to manage my identities, thus providing more secruity in "others" reading my emails. Is this not an option in Micro Oulook? I was planning to switch over after my soon to be ex, rebooted Windows while I was out of town and basically deleted everything. No back up disk. Starting all over :( You could put a password on your PST file so that a password would be needed to open it. In Outlook, right click on the root of your folder tree, choose Properties > Advanced > Change Password "...

Need help!
Hello, I need help. I generate charts, using macros, for my company. The macros are a fast tool when you have to do 40-50 charts a project. Well, my company had changed colors to the charts. As a result, I'm responsible updating the macros. Unfortantely, I'm not familiar with visual basic. The person who created the macros left the company. Can you please take a look at the excel files? Hopefully, this makes sense. Any suggestions and solutions are fully welcomed. Thanks in advanced! link: http://www.geocities.com/echenwick/ e-mail: echenwick...@SPAMMEyahoo.com ...

Multi-value paramter syntax...help
How do I write the syntax for a stored procedure that has one parameter but accepts multiple values. I am running a report from reporting services that can pass multiple values or pass all values to the stored procedure stored procedure 1: exec getcolor @color = blue....this runs fine. stored procedure 2: exec getcolors @colors in('red', 'white', 'blue').........bombs code is create procedure getcolors @colors nvarchar (50) as (select * from hrs_by_activity where color in(@colors) ) need help making this work. thanks in advance O...

Help with Business Portal Instalation
Hello, good afternoon group I have a problem, when i'm trying to install the Business Portal, the program checks the requirements shown me that everything is fine, but the button NEXT does not enable me to continue, which may be happening? I need your help, please. Thanks, Yandy ...

Totals based on Iif statement in subreport
Hello, I have some fields in a subreport -- ID, category and amount, that I need to total. For instance, I need a total of "amount" where each category is "200", not including any other cateogry. How do I do this? I Tried it, and it worked when there was only ONE RECORD in that particular ID. If there was a category 200 and another record for that ID that was 203, it's adding the amoutns for both categories together. What am I doing wrong? here is my if statement: =IIf([Category]=200,Sum([Amount]),0) Thank you! MN Mac, Place a calculated field in your ...

Macro Help
I'm a complete newbie when it comes to macros. H I J K 422 11 2.0 1.6 0.40 423 5 4.0 3.5 0.50 424 7 6.0 4.0 2.00 I highlighted this group of numbers in my spread sheet from H422 to K 424, and then I recorded a macro to sort on column K from highest to lowest. When done it looks like this: 7 6.0 4.0 2.00 5 4.0 3.5 0.50 11 2.0 1.6 0.40 I want this macro to work for every group of numbers like this throughout the spread sheet. Not all at once, but one at a time as I need them. So if I go d...

HELP ! Windows Server 2003 SP2 boots indefinitely
Hello, Since the last Microsoft Update (last week) two of my servers Windows server 2003 SP2, where the update was accepted, boot and reboot indefinitely. The other servers Win 2003 which didn't accept the update are running fine. How can I get rid of this last update ? Or can we correct this awful bug? I need your help ! Thanks You can uninstall it from the recovery console. From the recovery console command line navigate to; %SystemRoot%\$NtUninstallKBxxxxxx$\spuninst where xxxxxx is the KB article number you want to uninstall. and execute BATCH spuninst.t...

Need stacked chart help
Hi All, I have a problem with a stacked chart in excel 2000, i have used th search function but could not find a solution. The problem is as follows: I want to create a chart with one stacked bar (consisting of variables). And i also want another stacked bar (consisting of 2 variables). Only the proble is that there is a big difference in the numbers so one hardly shows up. Now i want both bars on different scales. Can somebody help me ? Thanks in advance -- wjvivee ----------------------------------------------------------------------- wjviveen's Profile: http://www.excelforum....

Outlook 98 with Domain need to auto start and login
I need a method to start outlook 98 on reboot and have it login into the network and if possible hide the folder. This is an alarm sending pc with windows 2k and we use outlook for the email piece and need to make sure that outlook is started and logged in so that alarms can be sent out. The alarms are critical and if a reboot occurs off hours this needs to auto start. thanks in advance ...

conditional formatting with FORMULA... Please HELP! #2
Hi all, I have price data in rows like this (each price takes up a cell): 5 5.43 6 6.25 6.50 8 15 9.25 9 8.75 8.50 8.50 8.50 8.50 13 I need to show trends via conditional formatting where on 3 or more consecutive days prices fluctuate EQUALLY by 0.25 or more each time, up or down... IE, with the above data, I would want to "flag" let's say, in red fill color, the sequence 6, 6.25, 6.50 as well as the group 9.25, 9, 8.75, 8.50, but NOT the group of 8.50, 8.50, 8.50. I have tried if/and/or statements again and again and come close on simpler tre...

More multi threading advice needed!
Hi, Need more help with this. I have an array of objects (Computer class). The class has a method that takes a while to run and I want it to run in it's own thread. So I have code like this: Private ProcessResultHandler As AsyncCallback = AddressOf ProcessResults For i = 0 To ComputerArray.GetUpperBound(0) sfh = AddressOf ComputerArray(i).UninstallAgent sfh.BeginInvoke(ProcessResultHandler, Nothing) Next i Public Sub ProcessResults(ByVal ar as IAsyncResult) sfh.EndInvoke(ar) End Sub Now, I need to know from within ProcessResult...

copying formula with worksheet name-need new formula to have another worksheet name
I want to be able copy a formula from b5 to b6 but have the worksheet name change, i.e.: I have 13 sheets in my workbook; the first sheet is called Summary which is where my formula presides. 2nd sheet,3rd sheet, etc. are called Sal Hrs_Jan10, Sal Hrs_Feb10, etc. My formula in b5 is =+'Sal Hrs_Jan10'!$C$15. I want b6 to say =+'Sal Hrs_Feb10'!$C$15 I want b7 to say =+'Sal Hrs_Mar10'!$C$15 Column a, cell a5 has Jan-10 (this is a date 1/1/2010 formatted as custom, mmm-yy). Can anyone help me??? Put this in B5: =3DINDIRECT("'Sal Hrs_"...

Help me make a timesheet?
Hi, I want to make a simple spreadsheet to keep track of times when I work from home. I can add and subtract in time units, but what I now want is to add 2 macro buttons which when clicked will insert the current system date and time into the next empty cell in the column (let's start at B2 for ease of explanation, so the second click of hte button will insert into B3, then B4 etc) One button will mark the start of a work session, the other will mark the end. Thanks for any assistance. -- Regards Jon Hi Jon, Are you aware that you can simply select the cell and then Ctrl+Shft+...

Help! Navigation bar disappeared after re-publishing!
I did some modifications on my navigation bar and re-published the entire website. When I reviewed my site, I noticed that the navigation bar was gone. I called Hostmonster who hosts my website. They uninstalled and re-installed the FrontPage Extension twice and renamed the access file, and each time I re-published the site. But to no avail. Does anybody have an idea? I am at my wit's end. Monika If you have a website give us a link to it so we can it and help you Out? "yogalady" <yogalady@discussions.microsoft.com> wrote in message news:1B6DDF78...

Excel Help....
I am trying to populate a spreadsheet with multiple information. I have a listing of counties that I want a single cell to identify (maybe a drop down?)in an already populated worksheet. Then I want the cell for "tax" to automatically populate the tax code associated with that county with the county entered. Can anyone help.... Hi use Data - Validation and VLOOKUP. See: http://www.contextures.com/xlFunctions02.html http://www.contextures.com/xlDataVal01.html -- Regards Frank Kabel Frankfurt, Germany "Jen" <Jen@discussions.microsoft.com> schrieb im Newsbeitrag n...

PLEASE HELP!!!!!!!
I have already done the regedit Level1Remove. Most everything works fine. My problem is that when I try to open an attachment that is a .jpg it wants me to save each individual file to disk before I can open it. I receive enormus amounts of jpegs a day and need to scan through them quickly without saving them. PLEASE HELP TO BYPASS THIS PROBLEM!!!!!! ...

Help with InputBox and MsgBox
I want an InputBox and MsgBox to display several lines on text and want to be able to hit carriage-return so that the text reads like a paragraph. I have the following :- Title = "Please Enter Password to LOCK ALL the sheets" MSG1 = ("Enter Password and Select OK") resp1 = InputBox(MSG1, Title) How could I format the above to say have a carriage return after "Enter Password"? -- Thank U and Regards Ann MSG1 = "Enter Password" & vbCr & "and Select OK" vbCr is a "constant" and is the same as Chr(13) which is th...

Help with tax formula for marginal tax rates
I am trying to make a single formula that will calculate a person's taxable income assuming the six different marginal tax rates that range from 10% to 35%, as income increases For a single taxpayer, the marginal tax rates and the associated income amounts are: 10% up to $8,350 15% up to $33,950 25% up to $82,250 28% up to $171,550 33% up to $372,950 35% over $372,950 I am using "IF" statements to test for each marginal tax rate. I began the formula as follows, but it returns a value error. Cell D36 is the taxpayer's income =IF(D36<=8350,D36/...

Help on Queue
Hi, Can someone explain me and help me out about how to get the queue system working? im not getting it... * When i try to send a email to: support@mydomain.com <-- this didnt work out... the email dont get on the queue to assign later on... in the email router i have estabelish a rule for support@mydomain.com to the crm user that have the queue... * When i try to send a email to: support@mydomain.com; myuser@mydomain.com <-- this works .. the mail gets in the user queue and is assigned to my user... * if i try send a email to support@mydomain.com ; anotheruser@mydomain.com th...

Help: Messages Disappear from Inbox Current View is set to Message
Hi: We are using Outlook 2002 SP2 and XP Pro SP1. Messages are disappearing from my inbox. I checked google and MS knowledge base. The current view for my inbox is set to message and not unread messages. The only other twist is that I have two accounts one is a pop3 and the other is imap. These accounts have two separate inboxes in their own folders. Any suggestions, TIA, Mariela I would double check your rules to make sure people are not being added to your junk senders list by accident. Dave >-----Original Message----- >Hi: > >We are using Outlook 2002 SP2 and XP ...

Loan help
I am getting a loan that will be put into my checking account for my use however I want. There is no asset as colateral. I will just be recieving cash. I cannot get Money 2003 to create a loan that deposits money into my account. Do I just have to create a deposit into my checking account? The thing I don't like about that is money treats it as an income with a category when it is really more like a transfer. The budget and net worth features don't work correctly like that. Just create it as a deposit. I know what you mean about it being treated as income, but it is, althoug...

Attachement Issues
Please help: I am running Outlook 2002. When I send email with attachments the email does not move from my 'Outbox' to my 'Sent Items' folder, causing the email to be sent multiple times. This does not happen with normal emails. My recipients are receiving multiple copies of the same email and I have no idea what the problem is. Any assistance is greatly appreciated. Jeremy. Does this still happen when you disable the outgoing virusscan? -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Backup and Restore -Create an Office XP CD slipstreamed...