Error when pasting Formula

[dave_br]

Guys,

i have written a long IF formula that has 9 levels of nesting at its maximum. if i use it in the workbook i made it in, it works fine but if i paste it into another workbook the 8 & 9 levels do not work and i get the error:

Excel was unable to correctly paste all of the data due to incompabilities with the current file format. please review the paste region to update your data.

any ideas what i have to change to make this work?

currently im pasting the data into the workbook the formula works on and then re saving as the file i want!

thanks
dave

 

[JackDanIce]

First reaction would be you shouldn't really need an IF statement nested that deep. May be an idea to post your formula and see if an alternative can be suggested, which may then copy over easier..

 

[dave_br]

The formula i have been using is this

=IF(N2>=60%,"40",IF(N2>=55%,"35",IF(N2>=50%,"30",IF(N2>=45%,"25",IF(N2>=40%,"20",IF(N2>=35%,"15",IF(N2>=30%,"10",IF(N2>=27%,"5",IF(N2>=25%,"2.5",)))))))))

the reason for the levels is to offer a percentage discount depending upon a buy in margin i.e. >60% buy in means i can give 40% discount. it seems to work fine in the sheet i created the formula in, just doesn't like being copied on my pc at the moment!

dave

 

[JackDanIce]

I think using a combined INDEX and MATCH formula would be easier and you can add additional combinations to it if required and should be reasonably easy to copy to other sheets.

You need to set up a table of your Percentage values and associated discount amounts and then the above formula, i.e.:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 8pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 70px"><COL style="WIDTH: 55px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 88px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Percentage</TD><TD style="FONT-WEIGHT: bold">Discount</TD><TD> </TD><TD style="FONT-WEIGHT: bold">Test (N2)</TD><TD style="FONT-WEIGHT: bold">Discount value</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">60%</TD><TD style="TEXT-ALIGN: right">40</TD><TD> </TD><TD style="TEXT-ALIGN: right">27%</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ccffff; TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">55%</TD><TD style="TEXT-ALIGN: right">35</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">50%</TD><TD style="TEXT-ALIGN: right">30</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">45%</TD><TD style="TEXT-ALIGN: right">25</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">35%</TD><TD style="TEXT-ALIGN: right">15</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">30%</TD><TD style="TEXT-ALIGN: right">10</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">27%</TD><TD style="TEXT-ALIGN: right">5</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">25%</TD><TD style="TEXT-ALIGN: right">2.5</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=INDEX($A$2:$B$9,MATCH(D2,$A$2:$A$9,-1),2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Try this in a dummy sheet and then move to where you want it. Best if you experiment with it and try to understand how it works

 

[jasonb75]

Maybe something like

=lookup(N2,{0,0;0.25,2.5;0.27,5;0.3,10;0.35,15;0.4,20;0.45,25;0.5,30;0.55,35;0.6,40)

First guess you were copying from an excel 2007 / 2010 workbook to a 2003 workbook which has a limit of 7 nested functions.

Another option would be a lookup table, much easier to edit if you think you're being a little too generous with some discount levels.

 

[dave_br]

thanks guys, it was between 2003 and 2010 so thats why but looks like there is a better way of doing it also so will have a play with that!!

dave