#VALUE! error on simple formula using drop down menu from input cell

%3CLINGO-SUB%20id%3D%22lingo-sub-3283815%22%20slang%3D%22en-US%22%3E%23VALUE!%20error%20on%20simple%20formula%20using%20drop%20down%20menu%20from%20input%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3283815%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EWe%20run%20our%20business%20off%20spread%20sheets%20and%20having%20just%20moved%20to%20O365%20from%20Google%2C%20I%20am%20having%20a%20major%20problem.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20get%20a%20%23VALUE!%20error%20when%20multiplying%20a%20simple%20number%20(hours%20worked)%20by%20a%20billing%20rate%20(%C2%A3%20per%20hour).%20E.G%20%3Dsum(8%5Bhours%20worked%5D*%20%C2%A321.50%20%5Bbilling%20rate%5D).%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20billing%20rate%20is%20being%20drawn%20from%20a%20cell%20which%20has%20drop%20down%20options%20(%C2%A320.00%2C%20%C2%A320.50%2C%20%C2%A321.00%2C%20%C2%A321.50%20etc).%20But%20I%20keep%20getting%20a%20%23VALUE!%20error%20on%20excel%20browser%2C%20but%20it%20works%20fine%20in%20the%20app%20version.%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20is%20this%3F%26nbsp%3B%3C%2FP%3E%3CP%3ESurely%20excel%20browser%20can%20calculate%20a%20simple%20formula%20if%20there%20is%20a%20drop%20down%20menu%20in%20an%20input%20cell%3F%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3283815%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284461%22%20slang%3D%22en-US%22%3ERe%3A%20%23VALUE!%20error%20on%20simple%20formula%20using%20drop%20down%20menu%20from%20input%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284461%22%20slang%3D%22en-US%22%3EHi%20Riny%2C%20problem%20is%20fixed.%20There%20is%20a%20weird%20quirk%20where%20the%20drop%20down%20list%20numbers%20must%20be%20formatted%20General%20on%20the%20browser%20(not%20same%20case%20on%20app%20version%20where%20they%20can%20be%20currency%20or%20number).%3CBR%20%2F%3EMany%20thanks%20for%20your%20help%20on%20this.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284384%22%20slang%3D%22en-US%22%3ERe%3A%20%23VALUE!%20error%20on%20simple%20formula%20using%20drop%20down%20menu%20from%20input%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284384%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1361684%22%20target%3D%22_blank%22%3E%40Alex_ST1015%3C%2FA%3E%26nbsp%3BHardly%20ever%20use%20the%20on-line%20version%20so%20can't%20really%20tell.%20But%20it%20should%20work%20the%20same%20as%20in%20the%20desktop%20version.%3C%2FP%3E%3CP%3EPerhaps%20you%20can%20share%20a%20link%20to%20the%20file%20with%20the%20problem%20you%20describe.%20Then%20I%2C%20or%20someone%20else%2C%20can%20open%20it%20in%20Excel%20for%20the%20Web%20and%20have%20a%20closer%20look.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284324%22%20slang%3D%22en-US%22%3ERe%3A%20%23VALUE!%20error%20on%20simple%20formula%20using%20drop%20down%20menu%20from%20input%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284324%22%20slang%3D%22en-US%22%3EHi%20Riny%3CBR%20%2F%3EI%20have%20downloaded%20and%20this%20works%20fine%20and%20exactly%20as%20i%20would%20expect.%20The%20problem%20only%20occurs%20on%20the%20browser%20version%20of%20excel%2C%20rather%20than%20the%20app%20version.%20I%20seem%20to%20have%20tried%20everything.%3CBR%20%2F%3EI%20really%20appreciate%20your%20help%20on%20this%20as%20desperate%20for%20a%20solution.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284261%22%20slang%3D%22en-US%22%3ERe%3A%20%23VALUE!%20error%20on%20simple%20formula%20using%20drop%20down%20menu%20from%20input%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284261%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1361684%22%20target%3D%22_blank%22%3E%40Alex_ST1015%3C%2FA%3E%26nbsp%3BAttaching%20a%20working%20example.%20Perhaps%20you%20can%20work%20out%20what's%20different%20in%20your%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284220%22%20slang%3D%22en-US%22%3ERe%3A%20%23VALUE!%20error%20on%20simple%20formula%20using%20drop%20down%20menu%20from%20input%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284220%22%20slang%3D%22en-US%22%3EThe%20validation%20list%20is%20in%20another%20tab%20and%20simply%20a%20list%20of%20numbers%2C%20formatted%20to%20%C2%A3(currency).%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284201%22%20slang%3D%22en-US%22%3ERe%3A%20%23VALUE!%20error%20on%20simple%20formula%20using%20drop%20down%20menu%20from%20input%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284201%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1361684%22%20target%3D%22_blank%22%3E%40Alex_ST1015%3C%2FA%3E%26nbsp%3BI%20suspect%20that%20the%20billing%20rates%20are%20not%20recognised%20as%20numbers.%20How%20exactly%20did%20you%20set-up%20the%20data%20validation%20list%3F%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20you%20don't%20need%20the%20SUM%20function.%20Just%20%3DC6*D6%20is%20sufficient.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284125%22%20slang%3D%22en-US%22%3ERe%3A%20%23VALUE!%20error%20on%20simple%20formula%20using%20drop%20down%20menu%20from%20input%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20Many%20thanks%20for%20your%20response.%20Please%20see%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Alex_ST1015_0-1649835882577.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F363698i18E123B280822D38%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Alex_ST1015_0-1649835882577.png%22%20alt%3D%22Alex_ST1015_0-1649835882577.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20billing%20rate%20column%20has%20the%20drop%20down%20in%20it%2C%20which%20pulls%20from%20a%20list%20of%20rates%20using%20Data%20Validation.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3284001%22%20slang%3D%22en-US%22%3ERe%3A%20%23VALUE!%20error%20on%20simple%20formula%20using%20drop%20down%20menu%20from%20input%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284001%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1361684%22%20target%3D%22_blank%22%3E%40Alex_ST1015%3C%2FA%3ESare%20it%20can%2C%20but%20can%20you%20upload%20a%20screenshot%20showing%20how%20exactly%20you%20enter%20the%20formula%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi

We run our business off spread sheets and having just moved to O365 from Google, I am having a major problem. 

I get a #VALUE! error when multiplying a simple number (hours worked) by a billing rate (£ per hour). E.G =sum(8[hours worked]* £21.50 [billing rate]). 

The billing rate is being drawn from a cell which has drop down options (£20.00, £20.50, £21.00, £21.50 etc). But I keep getting a #VALUE! error on excel browser, but it works fine in the app version. 

Why is this? 

Surely excel browser can calculate a simple formula if there is a drop down menu in an input cell? 

Please help!!

8 Replies

@Alex_ST1015Sare it can, but can you upload a screenshot showing how exactly you enter the formula? 

@Riny_van_Eekelen  Many thanks for your response. Please see below:

 

Alex_ST1015_0-1649835882577.png

The billing rate column has the drop down in it, which pulls from a list of rates using Data Validation. 

@Alex_ST1015 I suspect that the billing rates are not recognised as numbers. How exactly did you set-up the data validation list?

By the way, you don't need the SUM function. Just =C6*D6 is sufficient.

The validation list is in another tab and simply a list of numbers, formatted to £(currency).

@Alex_ST1015 Attaching a working example. Perhaps you can work out what's different in your file.

Hi Riny
I have downloaded and this works fine and exactly as i would expect. The problem only occurs on the browser version of excel, rather than the app version. I seem to have tried everything.
I really appreciate your help on this as desperate for a solution.

@Alex_ST1015 Hardly ever use the on-line version so can't really tell. But it should work the same as in the desktop version.

Perhaps you can share a link to the file with the problem you describe. Then I, or someone else, can open it in Excel for the Web and have a closer look. 

Hi Riny, problem is fixed. There is a weird quirk where the drop down list numbers must be formatted General on the browser (not same case on app version where they can be currency or number).
Many thanks for your help on this.