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

=G19*MIN(200,50+25*J19

This works great!

Thank you so much!

Could you maybe explain what this formula does?


Thanks again!  :cool

--
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 1:53:31 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
456 Views

Similar Articles

[PageSpeed] 15

Perhaps you didn't see my original post or Ken Wright's post very early in
this thread?


-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Ekser" <Ekser.1f799z@excelforum-nospam.com> wrote in message
news:Ekser.1f799z@excelforum-nospam.com...
>
> =G19*MIN(200,50+25*J19)
>
> This works great!
>
> Thank you so much!
>
> Could you maybe explain what this formula does?
>
>
> Thanks again!  :cool:
>
>
> -- 
> 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 2:10:06 PM
LOL - I was struggling there too Don, thinking huh, what was wrong with the 
formula offered?

Ekser, in case you can't find the other post, here is what I posted wrt Don's 
formula:-

What you have is a number pattern, and because that pattern can be identified it
is usually quite easy to cater for all the options without resorting to long
formulas with nested IFs.

Your example

=IF(J19=2,G19*100,IF(J19=3,G19*125,IF(J19=4,G19*150,IF(J19=5,G19*175,IF(J19>5,G19*200)))))

It appears that every time you add 1 to J19, the value you multiply G19 by goes
up by 25.  This means that if we take 50 out of every part of that equation
(Don't worry, we'll put it back), you would get something like

=IF(J19=2,G19*50,IF(J19=3,G19*75,IF(J19=4,G19*100,IF(J19=5,G19*125,IF(J19>5,G19*200)))))

The pattern is now more obvious in that a 2 in J19 gets you 2 lots of 25 (50), a
3 gets you 3 lots of 25 (75), a 4 gets you 4 lots of 25 (100) etc.  This means
that you could now use that value of J19 to replace all these hardwired values:-

=IF(J19=2,G19*(J19*25),IF(J19=3,G19*(J19*25),IF(J19=4,G19*(J19*25),IF(J19=5,G19*(J19*25),.......
I'll ignore the last part for a moment.

This can be condensed to

=G19*(J19*25) as it will give you the same answers for all but the last part,
and oh by the way, nearly forgot about your 50  :-)

=G19*(50+J19*25)

This now works for all your values EXCEPT that last one where you have said
anything GREATER than 5 gets a max factor of 200, so what we now do is wrap what
we already had in a MIN statement that says give me the smallest value of either
the result of the formula we already have, OR 200.  This means that for your
smaller values it will take the value from the formula, but if for example J19
was 8, the formula would return =G19*(50+8*25) which = G19*(250).  The MIN
formula however says that it will use the lower of the formula result OR 200,
and so hence the

=G19*MIN(200,50+25*J19)

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Don Guillett" <donaldb@281.com> wrote in message 
news:ujs29fnwEHA.2540@TK2MSFTNGP09.phx.gbl...
> Perhaps you didn't see my original post or Ken Wright's post very early in
> this thread?
>
>
> -- 
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Ekser" <Ekser.1f799z@excelforum-nospam.com> wrote in message
> news:Ekser.1f799z@excelforum-nospam.com...
>>
>> =G19*MIN(200,50+25*J19)
>>
>> This works great!
>>
>> Thank you so much!
>>
>> Could you maybe explain what this formula does?
>>
>>
>> Thanks again!  :cool:
>>
>>
>> -- 
>> 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
>>
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004 


0
ken.wright (2489)
11/5/2004 9:10:10 AM
Reply:

Similar Artilces:

formula inflexibility
The following equation does what most of what I want. All values are times with the format h:mm. =IF(OR(C2="YES",C2=""),IF(AND(E2>=D2,G2>=F2,I2>=H2),(E2-D2)+(G2-F2)+(I2-H2),"Time missing or mistyped"),"OFF"). An example of what it doesn't do is allow me to see a total for rows where G2<F2 and/or I2<H2 when E2>=D2. I'd like to make it flexible enough to SUM only the combinations that return a positive value while ignoring the others. This would allow me to have a running total instead of the text message "Time missing or m...

Visio 2002 + Visual Studio 6
Good Mornig, I work with Visual Basic 6 installed with Visual Studio 6 + SP5. I have worked all time with Visio 2000 without problem. Today i have installed Visio 2002. After that Visual Basic have stop to work. When I unistall that, VB6 work. Can anyone help me??? ...

Use Cell Containing Worksheet Name in Formula
I'm sure this can't be that hard, but I am trying to use a cell that contains a worksheet name in a formula, but can't get it to work. CellA1.value = "Sheet1" Formula is VLOOKUP($A$1,Sheet1!A1:B4,2,FALSE) but I don't want to use Sheet1, I want a reference to CellA1 that contains the sheet name. Any help would be appreciated! <jcurran13us@yahoo.com> wrote in message news:1167364842.447359.217560@h40g2000cwb.googlegroups.com... > I'm sure this can't be that hard, but I am trying to use a cell that > contains a worksheet name in a formula, but...

Formulas between workbooks
I have financial year data from July 1 06 to June 30 07 in one workbook and have started another workbook, today, July 1st, with the months July07 through to June 08. I used the same formula as I did in the original. I made a name in Name Manager and told it the value =SUM(June08:July07!$F$200) meaning I wanted all those 12 sheets to have the value at F200 added together and that cell with the Financial YTD values to have the total. In that cell to have that total, I have the name manager name TOTALIN as =SUM(TOTALIN) which worked in the first workbook. It doesn't work at all in th...

Formula that link worksheets
Can anyone help me with the formula protocols for linking information between worksheets in a workbook. I know it is not the ideal software for what i am trying to achieve but i am hoping to link income and expenditure worksheets to produce a monthly report for a club i am involved with. ie; data from sheet 1, 2, 3, & 4 linked to calculations in sheet 5 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ It sounds as if Excel *is* the ideal software for what you wa...

Length & Distance formula
Hi, I need a formula that will count based on the length of a vehicle, and a timed distance (counted in seconds) the vehicle is behind another vehicle. Here’s the breakdown; Satisfactory result for 40’ => 6 sec, 60' = >8 sec Less than Satisfactory 40' => 3 sec & <6 sec, & 60'= > 4 sec & <8 sec NOT Satisfactory 40' = 3 sec or less & 60' = 4 sec or less 40’ vehicles can be separated from the 60’ by the vehicle numbers. All 40’ vehicle numbers will start with 24, 90, and 91. So I have 3 columns to place a count. Satisfactory in Q3...

VLOOKUP formula to text
I'm using a vlookup formula where the user enters a single digit in a cell in one column, and text shows up in the adjacent cell in the next column. i.e. typing 2 in column E causes column F to read 'medical leave of absense' Is there a way to copy the text 'medical leave of absense' in column F into another file? this 2nd file is emailed to someone else, I don't want formulas, just text to show up in this file On Mon, 18 Jul 2005 10:19:04 -0700, "carrera" <carrera@discussions.microsoft.com> wrote: >I'm using a vlookup formula where the u...

OFFSET and array formulae
Are array formulae and OFFSET compatible? I have been having trouble using them together. I am trying to do an array calculation on two columns, simply dividing one by the other to generate an average. The problem is when I try to use offset with this, as my data is grouped every 7 cells. Any help is appreciated. Wazooli Wazooli, It would be best to show us some data, expected results, and your formula. -- HTH RP (remove nothere from the email address if mailing direct) "Wazooli" <Wazooli@discussions.microsoft.com> wrote in message news:67D71894-7FFB-41DF-9188-F...

Mail Relay #6
454 5.7.3 Client does not have perission to submit mail to this server. I've added the ip address to the relay settings on the smtp server. Clients can submit messages to the frontend server that works, but recieve this message when they try to relay through the backend. (telnet servername 25) they get this message when they type in mail from: myaddress@domain.com and are prompted with the above message.... NVM, i disabled annoymous access. "Gabe Matteson" <gmatteson@inquery.biz.nospam> wrote in message news:%23nVW0gyMGHA.532@TK2MSFTNGP15.phx.gbl... > 454 5.7.3 ...

calling conventions in VC++ 6
Microsoft's C++ compiler (for x86) supports four calling conventions: __cdecl, __stdcall, __fastcall and __thiscall. Does this mean that whenever defining a function, any of the above 4 calling conventions can be picked and added as specified in front of the function declaration? If there's no such a calling convention specified, then what's the default calling for a function? Are different calling conventions used for "global" functions, static and non-static class member functions? What does a calling convention of a function determine on its callers? Why __thiscal...

Pivot Table Formula Problem
I have a pivot table that has two row fields with a subtotal, and a data field. For example, the first row field is Customer, the second is Product, and the data is a count of that customer’s purchases of that product. The Customer field has a Sum subtotal. What I want is to divide each product count by that customer’s subtotal. I can not figure out how to refer to a subtotal in a pivot table formula, or how to do this if you cannot refer to the subtotal. thank you for any help and suggestions! Heather Excel 2007, PivotTable % of subtotal http://www.mediafire.com/file...

formula for age at future date
A1 is date of birth in 01/15/30 format. Need to show how old each person will be (years only) by end of 2010. Then need to indicate Y or N if they will be 65 or older at that time. MEJ try this. A1 is DOB. Type in B1 Today() Type this formula in C1 =YEAR(B1)-YEAR(A1) Type this in D1 =IF(C1>59,"Y","N") This will give you a N for anyone under 60 and a Y for older then 60. Hope this helps, Paul "MEJ" wrote: > A1 is date of birth in 01/15/30 format. Need to show how old each person > will be (years only) by end of 2010. Then need to indicate...

auto fill cells with "formulas" from within the same worksheet
Hello all I'm trying to have Excel auto fill adjacent cells in a row (B1:E1) after selecting a name from an in-cell dropdown list at A1. The source of the names in the list (A10:A13) and their corresponding "formulas" are at B10:E10, B11:E11, B12:E12, & B13:E13. Does anyone know how to write a macro or if-then statement, etc. that will automatically copy & paste special (formulas) to B1:E1 upon the selection of the name at A1 Thanks, btk btk, here's one way. Put this in B1: =VLOOKUP($A$1,$A$10:$E$13,COLUMN(),FALSE) and copy it to C1:E1. DDM "DDM's Micros...

Selecting rows or columns at warp 6!!
Some times when I am selecting a range of rows (1-???), or a range of columns (A-??), instead of it proceeding very slowly, and controllable, as it usually does, it goes warp 6, and before I know it, I am at row 1200, or column AA, when what I wanted was row 60, or column R. (for instance). There doesn't seem to be any logic behind it, but it is really honks me off. I am using WIN 98SE, and Office 98 As always any help is appreciated -- mkingsley ------------------------------------------------------------------------ mkingsley's Profile: http://www.excelforum.com/member.php?...

#Value #6
I am calculating percentages and in one cell the formula works and in the next it gives me a #value error, the formulas match perfectly just different cells. Can anyone explain this. This is the formula =IF(E11:E14="",0,SUMIF(E11:E14,">0")/COUNTIF(E11:E14,">0")) You sure you don't want a formula like: =IF(COUNT(E11:E14)=0,0,SUMIF(E11:E14,">0")/COUNTIF(E11:E14,">0")) or even: =IF(COUNTIF(E11:E14,">0")=0,0,SUMIF(E11:E14,">0")/COUNTIF(E11:E14,">0")) Todd Nelson wrote: > > I am ...

If formula help...
I want a formula in cell S2 to do the following... If R2 = W and Q2 is a positive number display Q2. If R2 = W and Q2 is a negative number display 100. If R2 = L and Q2 is a positive number display -100. IF R2 = L and Q2 is a negative number display Q2. What formula would I use for this? Thanks. Hi does the following formula give you what you want? =IF(OR(AND(R2="W",Q2>0),AND(R2="L",Q2<0)),Q2,IF(AND(R2="W",Q2<0),100,IF(AND(R2="L",Q2>0),-100,"check entry"))) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm .......

Formulas #34
I have multiple worksheets that have information listed like as follows: Column A AB1020 KH1018 KH1013 MM1021 AB1020 KH1018 KH1013 MM1021 AB1020 KH1018 KH1013 MM1021 Now on another worksheet I have a list of these items and their costs: AB1020 1.57 KH1018 2.07 KH1013 2.67 MM1021 3.17 I want to create a formula that will see the value in column A on sheet 1, find the match in column A on sheet 2 and input the data located from column b of sheet 2 in column b on sheet 1. Hi, RSL. You...

lookup formula's
=vlookup(((vlookup((vlookup(Lookup!X387:Y393,2,false)Main!A2:F7,6,false)B465:L500,3,false))) i know it's wrong cause it won't let me do it but i just don't kno what's wrong w/ it. Can someone pleas hel -- Message posted from http://www.ExcelForum.com Hi! >what's wrong w/ it. Well, the main problem is that you don't have a lookup value for any of the vlookup functions. You have to tell the lookup function what to lookup! Here's how you formula breaks down: vlookup(_______,Lookup!X387:Y393,2,false)- no lookup value vlookup(_______,Main!A2:F7,6,false)- n...

formulas #5
I need a formula for a tax calculation. Example - income at 400000 with tax rates of 18.62% for the 1st 200000, 20.87% for next 100000 and 25.39% for next 100000. I want to show the 3 separate tax amounts in different cells. I want a formula starting from the 400000 amount in the taxable income cell to show the following in the 3 cells. ie: taxable income 400000 200000@18.62 32240 100000@20.87 20870 100000@25.39 25390 Hi Terry, First off, 200000*.1862 = 37240 (32240) typo? B1 taxable income 400000 B2 =IF(B1>=200000,200000*0.1862,B1*0.1862) B3 =IF(B1<200000,0,IF(B...

Help with Formula in excell 2007
I am using columns F3, H3 I3 and J3. In column J3 my formula is =max(f3,h3) which is what i need but I want a formula that if I3 has a value then J3 I3=max(f3,H3),I3_>0,blank) but keep getting errors, I also tried 0 and none where the word blank is in the previous formula. Any help would be appreciated. Thanks Perhaps you mean in J3: =IF(I3<>"",MAX(F3,H3),"") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bspadt" wrote: > I am using columns F3, H3 I3 and J3. In column J3 my formula is =max(f3,h3) > which is what i n...

Will "IF THEN" Formula accomplish this for me?
Hello, In my worksheet below, I display budget amounts for each month of the year (the "Forecast" columns) which change to Actuals (the "Actual" columns) once a month has ended and the financials are completed. My "Full Year Forecast" column (the first column) needs to add Actual amounts for the months that have closed and Forecast amounts for the months with no Actuals yet. For example, if I have Actuals through March, my "Full Year Forecast" column should be adding Actuals for January, February, and March but Forecast amounts for April throu...

Changing the way relative references in formulas are adjusted when copied
Hello I have a very basic formula in cell B3 of a worksheet. This formula contains a reference to cell B9 in a different worksheet (same workbook). I would like to copy this formula three columns across, to cell E3. This normally adjusts the relative reference in the formula to cell E9. Here's the rub: I want the formula to reference cell B10 (ie one row down, same column). And when I copy the formula to cell H3 I would like the reference to change to cell B11 (again one row down, same column). Formula in cell: Copied to: I want it to change to: B3 =Sheet1!B9 E3 ...

returning a cells address based on a formula?
I have a data range of a1:d5 with sales figures. I have determined the lowest value using =MIN(A1:D5). What I need to know is what cell contains that lowest value (is it a3 or b2 or d4, etc.). The =CELL("address",min(a1:d5)) does NOT work. Any idaes? Thanks, DAVE Dave, Use the array formula, entered with Ctrl-Shift-Enter: =ADDRESS(MAX((A1:D5=MIN(A1:D5))*ROW(A1:D5)),MAX((A1:D5=MIN(A1:D5))*COL UMN(A1:D5))) HTH, Bernie MS Excel MVP "Dave" <sambi11@netscape.net> wrote in message news:77194972.0312291751.d2cbb56@posting.google.com... > I have a data range of a1:d5 ...

Requesting formula help
Hi I am looking for help in creating a formula. These are my variables: Estimated Beta of stock is - 1.5 Current Stock price is - $50 Estimated price is - $60 Standard Deviation is - $2.50 Risk free rate of return is - 4% Market risk premium is - 8% Looking for the probability that the stock is overvalued? Thank you, brad ...

Formula to count number of days that match, when comparing one date range to another
I am trying to count any matching days when comparing one date range to another. Any formula suggestions would be appreciated. For example: Range 1 Start Date1 Thru Date1 Start Date 2 Thru Date 2 Count Match 3/4/08 4/9/09 3/5/08 3/7/09 formula to show "3" 5/1/08 5/15/08 4/8/08 5/5/08 formula to show "5" Etc Not sure how you arrive at a result of 3 for the first group. There are 368 days that overlap. This formula returns 5 for the second group: =MAX(0,MIN(B3,D3)-MAX(A3,C3)+1) -- Biff...