Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Formula works in google sheets but not in Excel, how to make it works?

=IF(D2="","",LOOKUP(D2,{0,4.99,9.99,24.99,99.99,14 99.99},{"1","1.5","2.5",D2*0.1,(0.05*(D2-100))+10,(0.03*(D2-1500))+80}))

This formula works in google sheets but not in Excel on my PC.

How to make it works on Excel? "Using Excel 2007"

Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 829
Default Formula works in google sheets but not in Excel, how to make it works?

"Pat" wrote:
=IF(D2="","",LOOKUP(D2,{0,4.99,9.99,24.99,99.99,14 99.99},
{"1","1.5","2.5",D2*0.1,(0.05*(D2-100))+10,(0.03*(D2-1500))+80}))
This formula works in google sheets but not in Excel on my PC.
How to make it works on Excel? "Using Excel 2007"


Try:

=IF(D2="","",CHOOSE(MATCH(D2,{0,4.99,9.99,24.99,99 .99,1499.99}),
"1","1.5","2.5",D2*0.1,(0.05*(D2-100))+10,(0.03*(D2-1500))+80))

However, at a minimum, I think the following is better:

=IF(D2="","",CHOOSE(MATCH(D2,{0,4.99,9.99,24.99,99 .99,1499.99}),
1,1.5,2.5,D2*0.1,0.(05*(D2-100))+10,(0.03*(D2-1500))+80))

I removed the double-quotes around the first 3 results so the formula
returns __numeric__ 1, 1.5 and 2.5 instead of __text__ "1", "1.5" and "2.5".
I presume that is what you intended.

Moreover, if D2 is calculated (a formula), the following might be more
reliable:

=IF(D2="","",CHOOSE(MATCH(ROUND(D2,2),
{0,4.99,9.99,24.99,99.99,1499.99}),
1,1.5,2.5,D2*0.1,0.(05*(D2-100))+10,(0.03*(D2-1500))+80))

The point is: D2 might __appear__ to be 4.99 due to formatting (e.g. Number
with 2 decimal places), but its __actual__ value might be less, for example
4.989.

In that case, you need to use ROUND(...,2) if you want to treat D2 as it
__appears__ (4.99).

Alternatively, you might use ROUND(...,2) in the formula in D2 instead.
Whether or not that is correct to do depends on the design of your
worksheet.

That is true for Google Sheets as well as Excel.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 230
Default Formula works in google sheets but not in Excel, how to makeit works?

On 04/03/2014 20:32, joeu2004 wrote:
"Pat" wrote:
=IF(D2="","",LOOKUP(D2,{0,4.99,9.99,24.99,99.99,14 99.99},
{"1","1.5","2.5",D2*0.1,(0.05*(D2-100))+10,(0.03*(D2-1500))+80}))
This formula works in google sheets but not in Excel on my PC.
How to make it works on Excel? "Using Excel 2007"


Try 2003 or earlier it will probably work OK there.

Try:

=IF(D2="","",CHOOSE(MATCH(D2,{0,4.99,9.99,24.99,99 .99,1499.99}),
"1","1.5","2.5",D2*0.1,(0.05*(D2-100))+10,(0.03*(D2-1500))+80))

However, at a minimum, I think the following is better:

=IF(D2="","",CHOOSE(MATCH(D2,{0,4.99,9.99,24.99,99 .99,1499.99}),
1,1.5,2.5,D2*0.1,0.(05*(D2-100))+10,(0.03*(D2-1500))+80))

I removed the double-quotes around the first 3 results so the formula
returns __numeric__ 1, 1.5 and 2.5 instead of __text__ "1", "1.5" and
"2.5". I presume that is what you intended.

Moreover, if D2 is calculated (a formula), the following might be more
reliable:

=IF(D2="","",CHOOSE(MATCH(ROUND(D2,2),
{0,4.99,9.99,24.99,99.99,1499.99}),
1,1.5,2.5,D2*0.1,0.(05*(D2-100))+10,(0.03*(D2-1500))+80))

The point is: D2 might __appear__ to be 4.99 due to formatting (e.g.
Number with 2 decimal places), but its __actual__ value might be less,
for example 4.989.

In that case, you need to use ROUND(...,2) if you want to treat D2 as it
__appears__ (4.99).

Alternatively, you might use ROUND(...,2) in the formula in D2 instead.
Whether or not that is correct to do depends on the design of your
worksheet.

That is true for Google Sheets as well as Excel.


When I try it in XL2010 it faults any attempt to put a formula in the
results array list. It only accepts simple manifest constants.

It won't accept PI() for instance.


--
Regards,
Martin Brown
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 829
Default Formula works in google sheets but not in Excel, how to make it works?

"Martin Brown" wrote:
"Pat" wrote:
=IF(D2="","",LOOKUP(D2,{0,4.99,9.99,24.99,99.99,14 99.99},
{"1","1.5","2.5",D2*0.1,(0.05*(D2-100))+10,(0.03*(D2-1500))+80}))
This formula works in google sheets but not in Excel on my PC.
How to make it works on Excel? "Using Excel 2007"


Try 2003 or earlier it will probably work OK there.


No it doesn't. At least, not in Excel 2003.


"Martin Brown" wrote:
On 04/03/2014 20:32, joeu2004 wrote:
=IF(D2="","",CHOOSE(MATCH(D2,{0,4.99,9.99,24.99,99 .99,1499.99}),
"1","1.5","2.5",D2*0.1,(0.05*(D2-100))+10,(0.03*(D2-1500))+80))

[....]
When I try it in XL2010 it faults any attempt to put a formula
in the results array list. It only accepts simple manifest constants.


..... Which is why I effectively replaced LOOKUP with CHOOSE.

From the Excel LOOKUP help page:

[BEGIN QUOTE]
LOOKUP(lookup_value,lookup_vector,result_vector)

Lookup_value is a value that LOOKUP searches for in the first vector.
Lookup_value can be a number, text, a logical value, or a name or reference
that refers to a value.

Lookup_vector is a range that contains only one row or one column. The
values in lookup_vector can be text, numbers, or logical values.
[END QUOTE]

"Text, number or logical values". Nothing there about expressions. A
little experimentation would have confirmed:

=LOOKUP(5,{0,4.99,10},{1,2,3})

works, but:

=LOOKUP(5,{0,4.99,10},{1,2+0,3})

raises a syntax error.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2002: How to make the formula works ? Mr. Low Excel Discussion (Misc queries) 8 June 18th 07 01:57 PM
Macros: Step Thru Works, Run Works, Keyboard Shortcut Locks up BEEJAY Excel Programming 2 October 3rd 06 06:46 PM
Excel Addin works that works on a template workbook s.jay_k Excel Programming 0 February 15th 06 08:31 PM
How do I convert works file to excel without works software? CatMB Excel Discussion (Misc queries) 1 June 21st 05 04:12 PM
how do I make excel 2003 my default spreadsheet over ms works spr. steve Setting up and Configuration of Excel 1 December 23rd 04 09:03 PM


All times are GMT +1. The time now is 08:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"