Excel 2010 to Google Spreadsheet

[NeverBoring]

I hope someone can translate an Excel 2010 formula to a Google Spreadsheet formula. The formula used works in Excel and is transfered into Google Spreadsheet correctly. The browser for Windows 7 Pro 64 PC is Opera 11 and for Xoom, OfficeSuite Pro. The formula is there, however, it does not show a result. In other words, most of the elementary formulas that I wrote are fine. This formula was provided via someone (sorry I forget name) here at MrExcel.com.

The formula is:=SUMPRODUCT(--(B7:B2343=""),H7:H2343-G7:G2343), and is used to provide a running credit and debit balance. Thank you.

 

[Aladin Akyurek]

I hope someone can translate an Excel 2010 formula to a Google Spreadsheet formula. The formula used works in Excel and is transfered into Google Spreadsheet correctly. The browser for Windows 7 Pro 64 PC is Opera 11 and for Xoom, OfficeSuite Pro. The formula is there, however, it does not show a result. In other words, most of the elementary formulas that I wrote are fine. This formula was provided via someone (sorry I forget name) here at MrExcel.com.

The formula is:=SUMPRODUCT(--(B7:B2343=""),H7:H2343-G7:G2343), and is used to provide a running credit and debit balance. Thank you.

In case the -- bit is not understood, try to switch to one of...

=SUMPRODUCT((B7:B2343="")+0,H7:H2343-G7:G2343)

=SUMIF(B7:B2343,"=",H7:H2343)-SUMIF(B7:B2343,"=",G7:G2343)

 

[NeverBoring]

Thank you for your help. The second one worked great.

I have one additional formula, which I require assistance. May I submit it to you?

 

[NeverBoring]

This reply adds information.
Actually, it works on the PC, not on the Xoom. The return is : ~FUNCTION~NOT~IMPLEMENTED~.

There is one additional formula. =LOOKUP(9.9E307,I:I)
It appears that Google does not read LOOKUP function.

I realize I am asking a lot.

 

[Aladin Akyurek]

Thank you for your help. The second one worked great.

You are welcome. Did some test with formulas similar to the first one. Google spreadsheet has indeed no idea of such expressions.

I have one additional formula, which I require assistance. May I submit it to you?

Yes...

 

[Aladin Akyurek]

This reply adds information.
Actually, it works on the PC, not on the Xoom. The return is : ~FUNCTION~NOT~IMPLEMENTED~.

There is one additional formula. =LOOKUP(9.9E307,I:I)
It appears that Google does not read LOOKUP function.

I realize I am asking a lot.

The LOOKUP function does not exist.

Replacing LOOKUP with an equivalent INDEX/MATCH as in:

=INDEX(I:I,MATCH(BigNum,I:I,1))

where BigNum is written as you have done.

The formula for picking out the last numeric value from a reference
works in:

Excel
Calc (of OpenOffice)
Gnumeric

but, alas, not in:

Google Spreadsheet

This means that the latter's implementation of binary search differs from
the one depicted in:

http://www.mrexcel.com/forum/showthread.php?t=310278 (post #7)