#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,069
Default nesting if formula

=if(f6<56.16, f6*7.2,f6*7.9/2)

hi all
the above formula calculates f6, when less it does the far calculation
otherwise it performs the other,what i really want is for f6 to have 4 points
of reference from f6< 40 multply x3 ,from30x5 ,from 20 multiply 6 ,from 10
multiply x7
any help please
thanks john
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 913
Default nesting if formula

On Sun, 23 Nov 2008 03:53:00 -0800, john
wrote:

=if(f6<56.16, f6*7.2,f6*7.9/2)

hi all
the above formula calculates f6, when less it does the far calculation
otherwise it performs the other,what i really want is for f6 to have 4 points
of reference from f6< 40 multply x3 ,from30x5 ,from 20 multiply 6 ,from 10
multiply x7
any help please
thanks john


Is this want you want?

If the value in cell F6 is 40 or more you would like the formula to
return the value in cell F6 multiplied by 3
If the value in cell F6 is 30 or more (but less than 40) you would
like the formula to return the value in cell F6 multiplied by 5
If the value in cell F6 is 20 or more (but less than 30) you would
like the formula to return the value in cell F6 multiplied by 6
If the value in cell F6 is 10 or more (but less than 20) you would
like the formula to return the value in cell F6 multiplied by 7.

You have not defined what result you want if the value in cell F6 is
less than 10, so we assume that this will never happen.

Try this formula:

=F6*CHOOSE(MATCH(F6,{10,20,30,40}),7,6,5,3)

Hope this helps / Lars-Åke

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default nesting if formula

=F6*IF(F6<10,7,IF(F6<20,6,IF(F6<30,5,IF(F6<40,3,1) )))

this assumes that if f6=40
then the factor is 1
--
Gary''s Student - gsnu200815


"john" wrote:

=if(f6<56.16, f6*7.2,f6*7.9/2)

hi all
the above formula calculates f6, when less it does the far calculation
otherwise it performs the other,what i really want is for f6 to have 4 points
of reference from f6< 40 multply x3 ,from30x5 ,from 20 multiply 6 ,from 10
multiply x7
any help please
thanks john

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,069
Default nesting if formula


sorry ,if the value goes below 10 it needs to be 15x f6
"Lars-Ã…ke Aspelin" wrote:

On Sun, 23 Nov 2008 03:53:00 -0800, john
wrote:

=if(f6<56.16, f6*7.2,f6*7.9/2)

hi all
the above formula calculates f6, when less it does the far calculation
otherwise it performs the other,what i really want is for f6 to have 4 points
of reference from f6< 40 multply x3 ,from30x5 ,from 20 multiply 6 ,from 10
multiply x7
any help please
thanks john


Is this want you want?

If the value in cell F6 is 40 or more you would like the formula to
return the value in cell F6 multiplied by 3
If the value in cell F6 is 30 or more (but less than 40) you would
like the formula to return the value in cell F6 multiplied by 5
If the value in cell F6 is 20 or more (but less than 30) you would
like the formula to return the value in cell F6 multiplied by 6
If the value in cell F6 is 10 or more (but less than 20) you would
like the formula to return the value in cell F6 multiplied by 7.

You have not defined what result you want if the value in cell F6 is
less than 10, so we assume that this will never happen.

Try this formula:

=F6*CHOOSE(MATCH(F6,{10,20,30,40}),7,6,5,3)

Hope this helps / Lars-Ã…ke


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 913
Default nesting if formula

Then try this:

=F6*CHOOSE(MATCH(F6,{-1E+300,10,20,30,40}),15,7,6,5,3)

The -1E+300 could be be replaced with any value that is less than the
smallest possible value in F6.

/ Lars-Åke


On Sun, 23 Nov 2008 04:45:02 -0800, john
wrote:


sorry ,if the value goes below 10 it needs to be 15x f6
"Lars-Åke Aspelin" wrote:

On Sun, 23 Nov 2008 03:53:00 -0800, john
wrote:

=if(f6<56.16, f6*7.2,f6*7.9/2)

hi all
the above formula calculates f6, when less it does the far calculation
otherwise it performs the other,what i really want is for f6 to have 4 points
of reference from f6< 40 multply x3 ,from30x5 ,from 20 multiply 6 ,from 10
multiply x7
any help please
thanks john


Is this want you want?

If the value in cell F6 is 40 or more you would like the formula to
return the value in cell F6 multiplied by 3
If the value in cell F6 is 30 or more (but less than 40) you would
like the formula to return the value in cell F6 multiplied by 5
If the value in cell F6 is 20 or more (but less than 30) you would
like the formula to return the value in cell F6 multiplied by 6
If the value in cell F6 is 10 or more (but less than 20) you would
like the formula to return the value in cell F6 multiplied by 7.

You have not defined what result you want if the value in cell F6 is
less than 10, so we assume that this will never happen.

Try this formula:

=F6*CHOOSE(MATCH(F6,{10,20,30,40}),7,6,5,3)

Hope this helps / Lars-Åke





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,069
Default nesting if formula

thanks
your really good at these formulas seems to work well

"Lars-Ã…ke Aspelin" wrote:

On Sun, 23 Nov 2008 03:53:00 -0800, john
wrote:

=if(f6<56.16, f6*7.2,f6*7.9/2)

hi all
the above formula calculates f6, when less it does the far calculation
otherwise it performs the other,what i really want is for f6 to have 4 points
of reference from f6< 40 multply x3 ,from30x5 ,from 20 multiply 6 ,from 10
multiply x7
any help please
thanks john


Is this want you want?

If the value in cell F6 is 40 or more you would like the formula to
return the value in cell F6 multiplied by 3
If the value in cell F6 is 30 or more (but less than 40) you would
like the formula to return the value in cell F6 multiplied by 5
If the value in cell F6 is 20 or more (but less than 30) you would
like the formula to return the value in cell F6 multiplied by 6
If the value in cell F6 is 10 or more (but less than 20) you would
like the formula to return the value in cell F6 multiplied by 7.

You have not defined what result you want if the value in cell F6 is
less than 10, so we assume that this will never happen.

Try this formula:

=F6*CHOOSE(MATCH(F6,{10,20,30,40}),7,6,5,3)

Hope this helps / Lars-Ã…ke


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,069
Default nesting if formula

thanks could you explain what the 1e+300 does, works very well just curious
would be gratefull if you walk throuhg the formula as yours works
thanks
john

"john" wrote:

thanks
your really good at these formulas seems to work well

"Lars-Ã…ke Aspelin" wrote:

On Sun, 23 Nov 2008 03:53:00 -0800, john
wrote:

=if(f6<56.16, f6*7.2,f6*7.9/2)

hi all
the above formula calculates f6, when less it does the far calculation
otherwise it performs the other,what i really want is for f6 to have 4 points
of reference from f6< 40 multply x3 ,from30x5 ,from 20 multiply 6 ,from 10
multiply x7
any help please
thanks john


Is this want you want?

If the value in cell F6 is 40 or more you would like the formula to
return the value in cell F6 multiplied by 3
If the value in cell F6 is 30 or more (but less than 40) you would
like the formula to return the value in cell F6 multiplied by 5
If the value in cell F6 is 20 or more (but less than 30) you would
like the formula to return the value in cell F6 multiplied by 6
If the value in cell F6 is 10 or more (but less than 20) you would
like the formula to return the value in cell F6 multiplied by 7.

You have not defined what result you want if the value in cell F6 is
less than 10, so we assume that this will never happen.

Try this formula:

=F6*CHOOSE(MATCH(F6,{10,20,30,40}),7,6,5,3)

Hope this helps / Lars-Ã…ke


  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default nesting if formula

The 1E+300 wasn't in the message to which you were replying. If you go back
to the message which did include the 1E300, you'll see that it says:
"Then try this:

=F6*CHOOSE(MATCH(F6,{-1E+300,10,20,30,40}),15,7,6,5,3)

The -1E+300 could be be replaced with any value that is less than the
smallest possible value in F6. "

If you don't understand the notation, 1E+10 means 1 * 10^10, or one followed
by 10 noughts. 1E+300 is 1 followed by 300 noughts, in other words a very
large number, and -1E+300 is thus a very large negative number.
--
David Biddulph

"john" wrote in message
...
thanks could you explain what the 1e+300 does, works very well just
curious
would be gratefull if you walk throuhg the formula as yours works
thanks
john

"john" wrote:

thanks
your really good at these formulas seems to work well

"Lars-Åke Aspelin" wrote:

On Sun, 23 Nov 2008 03:53:00 -0800, john
wrote:

=if(f6<56.16, f6*7.2,f6*7.9/2)

hi all
the above formula calculates f6, when less it does the far calculation
otherwise it performs the other,what i really want is for f6 to have 4
points
of reference from f6< 40 multply x3 ,from30x5 ,from 20 multiply 6
,from 10
multiply x7
any help please
thanks john

Is this want you want?

If the value in cell F6 is 40 or more you would like the formula to
return the value in cell F6 multiplied by 3
If the value in cell F6 is 30 or more (but less than 40) you would
like the formula to return the value in cell F6 multiplied by 5
If the value in cell F6 is 20 or more (but less than 30) you would
like the formula to return the value in cell F6 multiplied by 6
If the value in cell F6 is 10 or more (but less than 20) you would
like the formula to return the value in cell F6 multiplied by 7.

You have not defined what result you want if the value in cell F6 is
less than 10, so we assume that this will never happen.

Try this formula:

=F6*CHOOSE(MATCH(F6,{10,20,30,40}),7,6,5,3)

Hope this helps / Lars-Åke




  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,346
Default nesting if formula

Hi,

Although you can do this type of problem with a nested if, if there were too
many IF's Excel would not be happy, so its probably good practice to consider
another solution, and one that give a lot of flexibility in the long run is
VLOOKUP. In this case create a lookup table in a range, say C1:D5 as follows

-10000 7
10 6
20 5
30 3
40 1

The -10000, just replace that with the smallest number you would ever get in
your data.

The the formula would be

=F5*VLOOKUP(F5,C$1:D$5,2,1)

If this helps, please click the Yes button.

Cheers,
Shane Devenshire
"john" wrote:

=if(f6<56.16, f6*7.2,f6*7.9/2)

hi all
the above formula calculates f6, when less it does the far calculation
otherwise it performs the other,what i really want is for f6 to have 4 points
of reference from f6< 40 multply x3 ,from30x5 ,from 20 multiply 6 ,from 10
multiply x7
any help please
thanks john

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,069
Default nesting if formula

thanks
the formula is good, can it be adjusted to include a range of numbers say
1-10 and return a static sum ,kind of like a minimum charge for the first few
items.thanks
john

"john" wrote:

thanks
your really good at these formulas seems to work well

"Lars-Ã…ke Aspelin" wrote:

On Sun, 23 Nov 2008 03:53:00 -0800, john
wrote:

=if(f6<56.16, f6*7.2,f6*7.9/2)

hi all
the above formula calculates f6, when less it does the far calculation
otherwise it performs the other,what i really want is for f6 to have 4 points
of reference from f6< 40 multply x3 ,from30x5 ,from 20 multiply 6 ,from 10
multiply x7
any help please
thanks john


Is this want you want?

If the value in cell F6 is 40 or more you would like the formula to
return the value in cell F6 multiplied by 3
If the value in cell F6 is 30 or more (but less than 40) you would
like the formula to return the value in cell F6 multiplied by 5
If the value in cell F6 is 20 or more (but less than 30) you would
like the formula to return the value in cell F6 multiplied by 6
If the value in cell F6 is 10 or more (but less than 20) you would
like the formula to return the value in cell F6 multiplied by 7.

You have not defined what result you want if the value in cell F6 is
less than 10, so we assume that this will never happen.

Try this formula:

=F6*CHOOSE(MATCH(F6,{10,20,30,40}),7,6,5,3)

Hope this helps / Lars-Ã…ke


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nesting of more than 7 in an IF formula Rachael F Excel Worksheet Functions 8 May 16th 08 11:52 AM
Can you help nesting a formula? Da Bossman Excel Worksheet Functions 7 August 13th 07 11:00 AM
NESTING FORMULA Tonya Excel Worksheet Functions 1 August 29th 06 10:21 PM
Nesting Formula DSCAVOTTO Excel Discussion (Misc queries) 4 May 22nd 06 06:36 PM
nesting another formula BSantos Excel Worksheet Functions 3 January 18th 06 10:22 PM


All times are GMT +1. The time now is 09:19 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"