What's wrong in this formula???? #5

Hm....

Using this formula:
=IF(J3>2,G3*(J3*25),IF(J3>3,G3*(J3*50),IF(J3>4,G3*(J3*75),IF(J3>5,G3*(J19*100)))))

I get this:

G3 = 3
J3 returns result of 4
So I should get (3*150) = 450....but I get 600.00

Same J3, and G3 = 2. 
I should have (2*150) = 300 ... but I get 400

G3=3
J3 returns 3
I should get (3*125) = 375... but I get 450



Any idea

--
Ekse
-----------------------------------------------------------------------
Ekser's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1140
View this thread: http://www.excelforum.com/showthread.php?threadid=27484

0
11/4/2004 11:48:27 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
287 Views

Similar Articles

[PageSpeed] 28

hi,
I see 2 things
1. all the numbers are greater than 2 so it is hitting the 
first condition every time.
in this situation i would try if(J3 = 2,......
2. you don't have a 2nd contitions in your last if.
try......If(J2 >5,G3*(J19*100),0)
regards

>-----Original Message-----
>
>Hm....
>
>Using this formula:
>=IF(J3>2,G3*(J3*25),IF(J3>3,G3*(J3*50),IF(J3>4,G3*
(J3*75),IF(J3>5,G3*(J19*100)))))
>
>I get this:
>
>G3 = 3
>J3 returns result of 4
>So I should get (3*150) = 450....but I get 600.00
>
>Same J3, and G3 = 2. 
>I should have (2*150) = 300 ... but I get 400
>
>G3=3
>J3 returns 3
>I should get (3*125) = 375... but I get 450
>
>
>
>Any idea?
>
>
>-- 
>Ekser
>----------------------------------------------------------
--------------
>Ekser's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=11400
>View this thread: 
http://www.excelforum.com/showthread.php?threadid=274849
>
>.
>
0
anonymous (74718)
11/4/2004 12:15:31 PM
When doing this I always start with the largest number and work my way down.
>5 >4 >3
but why don't you just try what I offered before?
=G19*MIN(200,50+25*J19)

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Ekser" <Ekser.1f739b@excelforum-nospam.com> wrote in message
news:Ekser.1f739b@excelforum-nospam.com...
>
> Hm....
>
> Using this formula:
>
=IF(J3>2,G3*(J3*25),IF(J3>3,G3*(J3*50),IF(J3>4,G3*(J3*75),IF(J3>5,G3*(J19*10
0)))))
>
> I get this:
>
> G3 = 3
> J3 returns result of 4
> So I should get (3*150) = 450....but I get 600.00
>
> Same J3, and G3 = 2.
> I should have (2*150) = 300 ... but I get 400
>
> G3=3
> J3 returns 3
> I should get (3*125) = 375... but I get 450
>
>
>
> Any idea?
>
>
> -- 
> Ekser
> ------------------------------------------------------------------------
> Ekser's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=11400
> View this thread: http://www.excelforum.com/showthread.php?threadid=274849
>


0
Don
11/4/2004 1:20:56 PM
Reply:

Similar Artilces:

wrong email address sends
How do I make Outlook send the correct email address and not a business account that I do not want going out with my messages I'm not sure if I understand your question correctly -- are you asking how to force Outlook to send messages through one account rather than another? If so, I need some more information from you before I can answer: What version of Outlook are you using, and if 98 or 2000, what mail support mode? If you're not sure, look at the second line of Help | About Microsoft Outlook -- it should say "Internet Mail Only" or "Corporate/Workgroup". ...

formatting question #5
I was wondering if anyone knew of a way to create a custom format for names. I use an ODBC import to obtain full names of people, and it comes in as such: "last_name ; first_name". I can't change that order in ODBC, thus I wind up with one column having both names, seperated by a semi-colon. I can code a solution, I was just wondering if there was a custom format capable of reversing the order of the text on either side of the semicolon. Any info would be appreciated, -Raj Hi Raj, This should work =TRIM(RIGHT(D17,LEN(D17)-(FIND(";",D17,1))))&" "...

Vlookup formula 05-06-10
Hello, I'm using the following Vlookup formula: =IF(ISNA(VLOOKUP(A4,'S:\Operations\Bloomberg\Investment Support\Dummies2Live\2010\May 10\[04052010.xls]Sheet1'!$A$4:$F$600,6,0)),"PRINT",0) This is linked to another spreadsheet - in this case 04052010.xls. Next day I will need to check file 05052010.xls and so on. To make life easier for my colleagues I wanted to add a cell with date where they can input the date and this will change the date in the formula (i.e. from 04052010 to 05052010) How can I link this formula to let's say cell A1 where I want to insert da...

Emails shown with wrong date in overview-screen?
hello NG, i have the following problem: i have made a constellation of folders in witch i sort my incoming and outgoing mail to. now i have found the following phaenomenon: in one of these folders i see a bunch of mail (sent by myself) witch all seem to be sent on the same date, 15. december 2004. but in reality this is the wrong date, they have been sent over a long period reaching back even until 2001. if i doubleklick on the mail and view it the correct date is shown. only in the "overview-screen" where all the mails are listed, the date is shown wrong. what can i do about ...

What wrong with sumproduct function?
I would like to sum all numbers, which match following conditions, =SUMPRODUCT(($B$1816:$B$2400=$A2402),(C$1816:C$2400<0),(C$1816:C$2400)) press ctrl + shift + enter but it returns zero, and I have checked it, zero should not be the result. Does anyone have any suggestions what wrong with sumproduct function? Thanks in advance for any suggestions Eric 1. Sumproduct is not an array function. Use Enter, not Ctrl-Shift-Enter. 2. You must convert false/trues to numbers. One way: =SUMPRODUCT(($B$1816:$B$2400=$A2402)*(C$1816:C$2400<0)*(C$1816:C$2400)) Regards, Fred "E...

Wrong Date/Wrong amount
In the Money 2004 check register it reports the last download date as well as the balance in the top left corner. The only problem is that it is totally wrong. It reports some bogus amount with a "downloaded" date of 10/9/2003. It is quite stubborn as nothing seems to affect it. Does anyone have any idea of how to correct this? Dirty Harry Harry Calahan wrote: > *In the Money 2004 check register it reports the last download dat > as > well as the balance in the top left corner. The only problem i > that > it is totally wrong. It reports some bogus amount wi...

Help please
I screwed something up and need help big-time. Somehow the default position of the cursor when I start a new document - or Outlook mail message - is in the right center part of the page. In fact, if I even open a document, it's formatted with everything scrunched over to the right. I can backspace and get the cursor to the right and it's not a big problem on documents with simple formatting, but for some complex documents, simply backspacing makes the formatting even worse. HELP PLEASW. -- rpbilleaud For a start, see http://word.mvps.org/FAQs/AppErrors/BlankDo...

Online Defrag #5
I have Exchange 2000 with SP3 and Post-SP3. The online defrag on one of Information Store (44GB) keeps on stopping and terminating. I setup it up to start at 3:00AM for 6 hours but still doing stopping and resuming. I setup the time in the Database properties Management Interval. Is this where it's suppose to be set? Is there a setup where the online defrag will start from the beginning and not just resuming from the previous online defrag that was stopped and terminated? I have not run the Offline defrag for almost 15 months and the last resumed online defrag showed only 400MB. I...

Challenging Formula Issue
I have been having serious trouble trying to sort this out. It is a little complicated and I will score major points with my supervisor if I can sort this out so here goes... I have four sheets of data that represent various international billing disputes. They all contain the date that the dispute was filed in column B and the issue type, which is one of 14 options, in column D. I would like to summarize the data in a table on a fifth sheet. The table needs to be organized by columns representing each month's disputes, and rows identifying the type of billing dispute. So...

What's going wrong in Outlook 2003?
Hello, When I installed the new Office 2003, I had no problems with Outlook 2003. I received and send mails whenever I want. A few days later, I get a message from outlook that Outlook could not make a connection with the server of my provider to get my mail on my computerb (I didn't change any settings!). I checked all my settings and there are good (I checked the mailserversettings, ports, etc) but the result is still the same: Outlook cannot make a connection with the mailserver. After that, I called my provider to ask them what to do. They told me that I must remove all the ...

wrong number in cell
In XP PRO & Excel 2002 I have a cell formated as General and unlocked. I try to enter a number { 08607100 } I hit enter and it changes to { 8607100 } no matter what I try it. This is another example of a problem in a cell I enter { 03E2 } hit enter and it changes to { 3.00E+02 }. Is there some bug in certain cells or with excel 2002 or is there some setting I am missing? Help -- Thanks MK pipco wrote: > In XP PRO & Excel 2002 I have a cell formated as General and unlocked. I > try to enter a number { 08607100 } I hit enter and it changes to { 8607100 &g...

Keeping data without losing Formula's
I have a worksheet (named monthly) with a column of data under a certain month (which will change each time data is entered - stocktake). However this column needs to be edited each month and has formula's in it so it will work (not always the same data in the column). I decided to put this data into a yearly sheet under the particular month. The only way I can do it at the moment is to copy and paste special. How do I (or is there a way to) put this data in a sheet that has all the months but under the month that is signified. In other words I don't want formula's in the yearl...

formula edit bar
all of the sudden my formula edit bar is gone. Does anyone know what I can do to restore?? Check it under view>formula bar -- Regards, Peo Sjoblom "Scot" <anonymous@discussions.microsoft.com> wrote in message news:00ed01c3d938$2d291950$a401280a@phx.gbl... > all of the sudden my formula edit bar is gone. Does > anyone know what I can do to restore?? Thank you. Duh, how could I miss that! Thanks! >-----Original Message----- >Check it under view>formula bar > >-- > >Regards, > >Peo Sjoblom > > >"Scot" <anonymous...

What Am I Doing Wrong?
The application I'm modifying is a fairly standard example of an MFC MDI app. There are several CToolBars created in MainFrm.cpp. The code to respond to the buttons on these toolbars is in the View.cpp file. One of these toolbars I modified to contain a slider. I derived a new class, CViewToolBar, from CToolBar and modified it to have a slider. I'm having problems with two major things: the correct way to include the new class information so that MainFrm knows about it to create the new toolbar, and how to get the view to respond to WM_HSCROLL messages from the slider. I had ...

Excel formula 11-22-09
I have a list of about 400 last names that I want to use to find in those names in other lists I have by doing an advanced filter. Many of the names are only a few letters and if I use them as is, as my criteria list, I get those names plus any that start with those letters. I know I can format the names so that it will only pick out the exact name on my list by typing ="="the name". I don't want to do that with all 400 names. How can I format the first name on the list and copy or drag it down with each row changing to the name on that row? Example: Adam...

Show/Hide Functions/Formulas
For some reason the keyboard shortcut (Ctrl+`) does not toggle show/hide function. Selecting Tools>Formula Auditing>Formula Auditing mode works fine. I am wondering if it is because I have regional (keyboard) settings set to UK English Extended. I know the `(grave) key works and have tried it with both Ctrl keys with no luck. Help please. "Smirnoff" <someone@nospam.invalid> wrote in message news:73972258-4ADC-4E40-B17B-A4E64E2B4B7F@microsoft.com... > For some reason the keyboard shortcut (Ctrl+`) does not toggle > show/hide function. > > Selecti...

Recurring split bills download wrong!
Money 2004: I have recurring bills with estimated amounts and splits. When I change the amount/splits and submit for payment, the check is being handled just fine through MSN Bill Pay. When I download the consummated transaction from Wells Fargo, however, Money is pulling in my ESTIMATED splits instead of my ACTUAL splits for that payment. Hence the splits never match the actual payment amount, and I get a warning. What a pain. This problem started when I switched from Wells Fargo bill pay to MSN. As I see it, though, it's Money's job to keep track of the splits internally, t...

result of formula in cell instead of formula itself...
I've got a spreadsheet where I need to search a table and return a value from a particular column in that table. Vlookup appears to be exactly what I'm looking for, however the first argument I need to pass it (the value to search for) is located in cell N2. Cell N2 is itself a formula and it appears that it is making vlookup fail. Is there a way I can tell the vlookup function to only use the result of the formula in that cell instead of following the formula itself? Thanks --cfortran On Mar 20, 3:05=A0pm, cfortran <cfort...@gmail.com> wrote: > I've got a spreadsheet w...

(DS/IS) consistency adjuster prior to moving Public folders from 5.5 to E2k3 and Nov 2004 Update Rollup
We're moving our exchange system from 5.5 to 2003 and we are ready to move the public folders. In my readings it says to run the (DS/IS) consistency adjuster on the 5.5 side to remove sids from the PF tree but the consistency adjuster does more than this which may have some side effects. Exchange 5.5 has a new rollup package dated Nov/Dec 2004 which has a fix for the consistency adjuster and exchange 2003 SP1. This rollup package fixes problems with the (DS/IS) consistency adjuster as read here: http://support.microsoft.com/?kbid=836489 The rollup states that the previous May 2004 ...

Exchange 2003 OWA on IIS 5.0
I recently had to rebuild my Exchange database, and afterwards users were complaining that they were getting different results when accessing their account through OWA as opposed to Outlook. I found a KB article about blowing away the current OWA virtual directories and having the server automatically recreate them. I attempted to do this, but it turns out the instructions were for IIS 6, and I'm running W2K with IIS 5. The virtual directories were not automatically created. I currently have no OWA and don't know how to re-establish that service on IIS 5. Any ideas??? Hi, ...

formula markers????
I now have office 2003 with xp sp2 and I see small green markers on cells where there is a formula on SOME of my spreadsheets. This is a nice feature but they do not appear on all my spreadsheets--why? Where is this neat feature controlled? hi, it is not exactly controled. it's built in. these markers are warnings or information about your formulas. warnings about incosistance formula in a columns and other stuff. if excel sences that nothing is wrong with the formual....no marker. if you right click the marker, you will get a popup menu which you can click ignore warnings. &...

Cell Formats in formulas
I have a worksheet set up with formulas to automatically copy values from another cell as it is changed. Is there a way to include the format of the reference cell (font, color) when referencing it in a formula. Example: when I change to a different symbol font in the referenced cell, the referencing cells only change the value, not the font, so I don't end up with the correct symbols in the referencing cell. No, it's not possible to do that using formulas, only format formulas can do are number formats and only using the text function Regards, Peo Sjoblom "C. Lewis"...

Contact data is in wrong field
Hi all - I need some help with a complicated problem. I am working on a PST file that was recovered from some old data. Unfortunately, the recovery put contact data in the wrong places. Here are a few examples: 1. Contact street address and city shows up in the "Follup up flag" field 2. Contact "Email display as" contains the display as information with the email address, but the email address field is empty 3. Many duplicates of data with slightly different information (Same contact might have 3 copies, one with a missing telephone number, another with a missing emai...

Lookup Value based on a formula
Hi all I am looking up a value in a table that is based around other formulas. When creating the Lookup the formula window shows the correct value. When the formula is entered the result is DIV/0!. Is there an easy way around it? or will I need the table based on hard values? Cheers Show us the actual formulas best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Cogeco news" <tmarshall3@cogeco.ca> wrote in message news:TZGGh.419$yc.257@read2.cgocable.net... > Hi all > > I am looking up a value in a table that is based around o...

formulas for rounding
Please can you help. I have a row of figures in excel spreadsheet and they do not add up to zero (they should add up to zero) Please could you let me know whether there is formula that can check the total and if the row of figures do not add to zero then it puts adds the total figure to a figure in the row to make the total equal zero i.e rounding. Thanks. Mas Take a look here: http://www.mcgimpsey.com/excel/pennyoff.html In article <605A2555-F61D-48AA-9A53-7415C70EC99A@microsoft.com>, Mas <Mas@discussions.microsoft.com> wrote: > Please can you help. > >...