Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-22-2012, 04:35 AM
danbl danbl is offline Windows 7 64bit Office 2003
Novice
 
Join Date: Feb 2012
Posts: 5
danbl is on a distinguished road
Default Formula calculation





I have a formula(below) that does a markup for charges. I am using senario manager to add different %increases which works fine., But when I try to decrease a % by using a negative number (-25%) the % is changed correctly but the math part increses the cost rather than decreasing the cost.

IF($D862<=50,($D862*J$2),IF(AND($D862>50,$D862<=15 0),($D862*K$2),IF(AND($D862>=151,$D862<=700),($D86 2*L$2),IF(AND($D862>=701,$D862<=10000),($D862*M$2) ,IF(AND($D862>=1001,$D862<=2000),($D862*N$2),IF(AN D($D862>=2001,$D862<=3000)*($D862*O$2),IF(AND($D86 2>=3001),$D862*P$2)))))))

Can anyone tell me why this is happening? and How to fix this?

Thanks
Reply With Quote
  #2  
Old 02-22-2012, 05:10 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows XP Office 2003
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,365
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Does it still happen using this alternative ?

=$D862*lookup($d862,{0,50,150,700,1000,2000,3000}, {J$2:P$2})

Maybe post a sample sheet with some of your data showing the problem?
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 02-22-2012, 09:38 AM
danbl danbl is offline Windows 7 64bit Office 2003
Novice
 
Join Date: Feb 2012
Posts: 5
danbl is on a distinguished road
Default

Thanks for the reply .... I am not well versed with the lookup function. I am attaching a sample worksheet that shows the formulas I am using. The yellow fields are the base values and the blue fields are the adjusted values that are derived from senario manager. Teh range fields are where senario manager makes the changes then the blue fields re-calculate and thus the new charges appear.
Attached Files
File Type: xls ProblemSheet.xls (304.5 KB, 2 views)
Reply With Quote
  #4  
Old 02-22-2012, 12:52 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows XP Office 2003
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,365
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Could you please add an example of what is going wrong in your sheet?
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #5  
Old 02-22-2012, 01:07 PM
excelledsoftware excelledsoftware is offline Windows 7 64bit Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 453
excelledsoftware will become famous soon enough
Default

Quote:
Originally Posted by danbl View Post
Thanks for the reply .... I am not well versed with the lookup function. I am attaching a sample worksheet that shows the formulas I am using. The yellow fields are the base values and the blue fields are the adjusted values that are derived from senario manager. Teh range fields are where senario manager makes the changes then the blue fields re-calculate and thus the new charges appear.
My suggestion even though you are not well versed with the lookup or vlookup functions you should learn them they really are as easy as IF formulas. The tool tips when creating a formula walk you through creating a vlookup but just so you have an idea.

=vlookup( < the formula which we will write in D1
=vlookup(The reference cell that the vlookup looks for in this example we will say A1
=vlookup(A1,Now you need to tell the formula what table to look in we will say b1:c12 in cell B1 type 1 B2 2 B3 3 etc., then in cell C1 type Jan then in C2 type Feb, C3 Mar etc.
=vlookup(A1,B1:C12 Now you tell the formula what column of the table to look in b1:c12 only has 2 columns but we want to look into the 2nd one so we would write 2
=vlookup(A1,B1:C12,2 Last part do you want the formula to find an exact match only or get something closest to it. 0 is an exact match and 1 is find the next closest. So it you put in 1.7 in A1 the vlookup will give you the value of 2 which will say Feb in our example.
=vlookup(A1,B1:C12,2,0)

There it is let me know if you have any questions or if you want me to try and correct your formula to Vlookup.

Thanks
Reply With Quote
  #6  
Old 02-22-2012, 03:07 PM
danbl danbl is offline Windows 7 64bit Office 2003
Novice
 
Join Date: Feb 2012
Posts: 5
danbl is on a distinguished road
Default

I am willing to learn the lookup function, but this is a time sensative project. If you go to senario and choose new, then show, you will see that even though the correct percentage is calculated in the 1001 to 2000 range, when the math happens to calculate the new charge instead of a decrease I am getting an increase in charge. If you look at the senario itself I am using a negative value to lower the overall percent markup for that price range.(Look at the highlighted bold red field when you run the senario)

Thanks
Attached Files
File Type: xls ProblemSheet.xls (305.0 KB, 1 views)
Reply With Quote
  #7  
Old 02-22-2012, 05:43 PM
danbl danbl is offline Windows 7 64bit Office 2003
Novice
 
Join Date: Feb 2012
Posts: 5
danbl is on a distinguished road
Default

I believe I have found part of the issue ... too many If statements??

Any thoughts? If you can convert the formula to a lookup that would be greatly appreciated. I am a visual learner so seeing it would help.
Reply With Quote
  #8  
Old 02-23-2012, 12:44 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows XP Office 2003
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,365
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Quote:
If you go to senario and choose new, then show,
I'm sorry I don't understand what you mean

Anyway you have 7 nested IF's which is the maximum allowed , so, no problem there

Also, there is an error in your formula

=IF($D137<=50,($D137*G$1),IF(AND($D137>50,$D137<=1 50),($D137*H$1),IF(AND($D137>=151,$D137<=700),($D1 37*I$1),IF(AND($D137>=701,$D137<=10000),($D137*J$1),IF(AND($D137>=1001,$D137<=2000),($D1 37*K$1),IF(AND($D137>=2001,$D137<=3000)*($D137*L$1 ),IF(AND($D137>=3001),$D137*M$1)))))))

I think the value in red should be 1000 not 10000 ?
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #9  
Old 02-23-2012, 04:35 AM
danbl danbl is offline Windows 7 64bit Office 2003
Novice
 
Join Date: Feb 2012
Posts: 5
danbl is on a distinguished road
Default

Thanks for pointing out the error!!!! Dumb mistake that makes a big difference ... I will check this out to see if it solves my issues. More later
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculation within Cells manich1 Excel 2 12-07-2011 02:59 PM
Excel Calculation Problem UnholySmoke Excel 2 09-14-2011 08:15 AM
Help with 'hours worked' calculation... Snvlsfoal Excel 1 08-11-2011 05:54 AM
Excel 2010 formula rounds - arithmetic average calculation YooNaa Kim Excel 1 06-07-2011 08:50 PM
Mail Merge Calculation Error Lsbutler2000 Mail Merge 1 06-22-2007 06:33 AM


All times are GMT -7. The time now is 07:24 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft