Microsoft Excel 2007?

Question

Trying to use the formula IF THEN ELSE with Excel 2007 IE: =IF B=ROLAND THEN C, IF C=1 AND B=ROLAND THEN D, SUM D ELSE IGNORE ... Now it's been a long time since I've used this format but I thought this could utilize names but the first line error coming up is #NAME? None of the solutions I've found at MS Excel seem to fit the problem I'm having if anyone can elaborate as to what I'm doing wrong I'd be grateful! (( B is a location C is a unit number D is a value ... I have eleven locations 15 units and each with different values on different days ... I'm at a loss))

Answer 1

UPDATE- based on the extra information you have added I think you probably should be using a SUMPRODUCT to check through the data and only add the numbers associated with the criteria you set



if the locations were in A1:A200 and the unit numbers were in B1:B200 and the values were in C1:C200



=SUMPRODUCT( --(A1:A200="ROLAND"), --(B1:B200=1), (C1:C200))



the "--" changes what would be returned as TRUE or FALSE to 1 or 0



|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||



can you post how you are entering the formula?



Excel is particular about the syntax for IF statements



=IF([logic_test], [result_if_true], [result_if_false])



you can nest up to a maximum of 7 IF statements



hope that helps, post some more info if we're missing the boat

Answer 2

There is some way. When you store the Excel dossier on Office 2007, If you wish Office 2003 to learn it you have got to put it aside as a .xls dossier. To do that you pass to File>Save As and Select Excel ninety seven-2003 Workbook. Hope this helped.

Answer 3

This is as close as I can come to putting your description of a formula into an actual formula. I gave the B,C and D the row #1 so as to make a reference to an actual cell. Roland is in quotes so that it is to be used as a value in B1. The "Sum D" at the end didn't make sense to me and didn't add anything to the formula's logic as far as I could make out.





=IF(B1="Roland",C1,IF(AND(C1=1,B1="Roland"),D1,""))

Answer 4

Historically, Excel had a limit of 256 characters per cell (up to Excel 2003 if I remember well).



The following notes are of some interest:



Note 700206 - XXL: MAX Size Limit for Excel Export for Table and Pivot

> "The Maximum limits with XXL Export: Number of Rows - 65536. Number of Columns - 256. Size of individual cell - 255 Characters."



Note 1429136 - OFFI: XXL_FULL_API Support for 1048576 rows in Office 2007

> Since, in Office 2007 Excel supports 10,48,576 rows, with this note we extend limit for XXL Export.



(Note that XXL_FULL_API is based on DOI, what you use).



So I think you can't get more from SAP for now.



Maybe OLE2 (the underlying technology used by DOI) has not this limit of 256 characters, but I wouldn't advise to use it because you might risk performance issues (unless you know how to use the Data Provider to transport big amount of data from frontend to SAP, if I understood well how it works).