Is this formula the problem?

[ss]

I am using this formula in my spreadsheet and it works fine:
{=SUM(IF(D1217>M6,D1217-IF(C12:C17<M6,M6,C12:C17)+1))}
I am using office 2007.
If however I save to an earlier version ofexcel there does seem to be
some compatabilty problems and someone who uses my spreadsheet has
particular problems with the above formula (apparently)
Would the above cause issues in earlier versions and if so what would be
a work around assuming there is one.
thanks

 

[Alex Plantema]

ss schreef in

I am using this formula in my spreadsheet and it works fine:
{=SUM(IF(D1217>M6,D1217-IF(C12:C17<M6,M6,C12:C17)+1))}
I am using office 2007.
If however I save to an earlier version ofexcel there does seem to be
some compatabilty problems and someone who uses my spreadsheet has
particular problems with the above formula (apparently)
Would the above cause issues in earlier versions and if so what would
be a work around assuming there is one.

We don't know whether this formula is correct if we don't know what you're trying to calculate.

 

[ss]

ss schreef in

We don't know whether this formula is correct if we don't know what you're trying to calculate.

The formula works ok in office 2007 its when others open it in previous
versions issues occur.

The cell in question is M11

http://i64.photobucket.com/albums/h194/scudo/Capture_zpsb354bf67.jpg

 

[Alex Plantema]

ss schreef in

The cell in question is M11

http://i64.photobucket.com/albums/h194/scudo/Capture_zpsb354bf67.jpg

Does this work when entered as an array formula?:

=SUM(IF(D1217>M6;D1217+1;M6))-SUM(IF(C12:C17<M6;M6;C12:C17))

 

[joeu2004]

[....]
The formula works ok in office 2007 its when others open it in previous
versions issues occur. The cell in question is M11
http://i64.photobucket.com/albums/h194/scudo/Capture_zpsb354bf67.jpg

Presumably, the formula was entered by pressing ctrl+shift+Enter instead of
just Enter, since we see the curly braces in the JPG file.

Define what you mean by "particular problems" that someone else sees. And
why "apparently"?

Is the other person being overly vague about the problem he/she incurs?

Is it possible the other person modified the formula, pressing just Enter
instead of ctrl+shift+Enter?

Is it possible the "particular problems" is an Excel error (e.g. #VALUE)
simply because the other person entered the wrong type of data into the
reference cells?

I am not familiar with too many earlier versions of Excel; really just Excel
2003. But off-hand, I do not see anything in the syntax of the formula that
might be a compatibility problem.

Note that if you save as "xls", it is not uncommon for Excel 2007 and later
versions to complain of non-existent compatibility "problems". I usually
ignore the warning and disable the check, after due diligence to ensure that
I did not unintentionally use a new feature.

Or perhaps the alleged compatibility problems are elsewhere, e.g. using a
color palette that is not supported in previous Excel versions. Of course,
that would not affect the operation of the formula above.

 

[ss]

I am using this formula in my spreadsheet and it works fine:
{=SUM(IF(D1217>M6,D1217-IF(C12:C17<M6,M6,C12:C17)+1))}
I am using office 2007.
If however I save to an earlier version ofexcel there does seem to be
some compatabilty problems and someone who uses my spreadsheet has
particular problems with the above formula (apparently)
Would the above cause issues in earlier versions and if so what would
be a work around assuming there is one.

[....]
The formula works ok in office 2007 its when others open it in
previous versions issues occur. The cell in question is M11
http://i64.photobucket.com/albums/h194/scudo/Capture_zpsb354bf67.jpg

Presumably, the formula was entered by pressing ctrl+shift+Enter instead
of just Enter, since we see the curly braces in the JPG file.

Define what you mean by "particular problems" that someone else sees.
And why "apparently"?

Is the other person being overly vague about the problem he/she incurs?

Is it possible the other person modified the formula, pressing just
Enter instead of ctrl+shift+Enter?

Is it possible the "particular problems" is an Excel error (e.g. #VALUE)
simply because the other person entered the wrong type of data into the
reference cells?

I am not familiar with too many earlier versions of Excel; really just
Excel 2003. But off-hand, I do not see anything in the syntax of the
formula that might be a compatibility problem.

Note that if you save as "xls", it is not uncommon for Excel 2007 and
later versions to complain of non-existent compatibility "problems". I
usually ignore the warning and disable the check, after due diligence to
ensure that I did not unintentionally use a new feature.

Or perhaps the alleged compatibility problems are elsewhere, e.g. using
a color palette that is not supported in previous Excel versions. Of
course, that would not affect the operation of the formula above.

Joeu, This probably does not explain the problem but this is the
correspondence below, so that you understand I have done the spreadsheet
and he then puts it on his website but as it is available for download
he tries to have a few version including open office to try and cater
for all. We both kind of stumble through this stuff but have no
particular skills in it but do the best we can :-(

And I quote...

"xls vs xlsx formats (Excel 2003 and 2007).
I am unable to save in xlsx because it removes the conditional
formatting in cell I13. This is of course a key function.

Apparently this is a common problem with xlsx formatting, I have tried
all roads including updating to the very latest LibreOffice version. No joy.

I understand that Excel 2007 and later can work ok with the old xls
format. So I ave 2 questions:

Does the attached work ok
Do you think it is possible to achieve the calculation in cell I13
without using conditional?

Don't burn your brain cells out on the second one, it is the most
complicated formula and it is beyond me how it completely works. I only
know that if the conditional is removed it does not.

 

[joeu2004]

[....]
this is the correspondence below [....]
"xls vs xlsx formats (Excel 2003 and 2007).
I am unable to save in xlsx because it removes the conditional formatting
in cell I13. [....]
I understand that Excel 2007 and later can work ok with the old
xls format.

Conditional formatting?!

"What does that have to do with the price of tea in China?"

First, is the array-entered SUM formula in I13?

Or is it the Conditional Formatting formula for I13 (or some other cell
altogether)?

Second, if you are delivering an "xls" file, why is the other person
converting it to "xlsx"?

I think it would be better to leave it as "xls". IIRC, the only problems
that creates is:

1. The user is limited to 65536 rows of data, even though XL2007 and later
allow up to 1,048,576 rows.

2. The user might encountered warnings when saving the file. Some might be
bogus "compatibility" warnings. Some might be legitimate warnings about the
use of new XL2007 functionality (e.g. IFERROR, AVERAGEIF, SUMIFS, COUNTIFS
etc), which XL2007 and later allow us to enter even though we are
compatibility mode. :-(

Finally, I'm not aware of any __real__ conditional formatting compatibility
problems going from "xls" to "xlsx". I don't doubt that XL2007 and later
might complain; compatibility checking is too conservative, flagging
non-errors as warnings. But I think that happens only on the first save,
which your partner would do, not the users of his website.

However, there might be issues that I am not aware of. I only know what I
stumble across myself or while helping someone else.

Suggestion.... Instead of posting a link to a JPG file, how about a link to
the Excel file itself.

I have have XL2003, XL2007 and XL2010. So I might be able to isolate
whatever the so-called compatibility problem is and indicate whether its
"real or Memorex".

If you are skittish about making the file public, email it or a download
password directly to me at joeu2004 "at" hotmail.com.

(Caveat: I am a US-English user. Some issues are specific to Regional and
Language Options control panel and to the regional flavor of Excel.)

 

[3Suk]

I am using this formula in my spreadsheet and it works fine:

{=SUM(IF(D1217>M6,D1217-IF(C12:C17<M6,M6,C12:C17)+1))}

I am using office 2007.

If however I save to an earlier version ofexcel there does seem to be

some compatabilty problems and someone who uses my spreadsheet has

particular problems with the above formula (apparently)

Would the above cause issues in earlier versions and if so what would be

a work around assuming there is one.

thanks

dear all,
just come back, I will read of the posts.
thanks for all yours help.
Rgds,
Patrick