Home

#VALUE! ERROR WITHOUT EXPLAINATIOIN

%3CLINGO-SUB%20id%3D%22lingo-sub-643648%22%20slang%3D%22en-US%22%3E%23VALUE!%20ERROR%20WITHOUT%20EXPLAINATIOIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643648%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20following%20formal%20has%20a%20%23value!%20error.%20I%20havent%20found%20the%20reason%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(((%24AW30%3A%24AY30)%2F3)*(%24AZ30))%2F(%24AZ30)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20cells%20in%20the%20formula%20are%20at%20zero%20when%20the%20stock%20market%20is%20closed.%20I%20have%20not%20yet%20tested%20the%20formula%20during%20market%20open%20because%20Im%20trying%20to%20finish%20before%20open.%20Also%20I%20want%20the%20spread%20sheet%20to%20work%20when%20market%20is%20closed%2C%20even%20though%20the%20data%20the%20formula%20looks%20at%20is%20zero.%20My%20spread%20sheet%20wont%20work%20if%20this%20formula%20returns%20this%20error.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-643648%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-643671%22%20slang%3D%22en-US%22%3ERe%3A%20%23VALUE!%20ERROR%20WITHOUT%20EXPLAINATIOIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-643671%22%20slang%3D%22en-US%22%3E%3CP%3EFOLLOW%20Up%3A%3C%2FP%3E%3CP%3EI%20should%20mention%20that%20this%20formula%20came%20from%20these%20instructions%3A%3C%2FP%3E%3COL%3E%3CLI%3EFind%20the%20average%20price%20the%20stock%20traded%20at%20over%20the%20first%20five-minute%20period%20of%20the%20day.%20To%20do%20this%2C%20add%20the%3CSPAN%3E%26nbsp%3Bhigh%2C%20low%2C%20and%20close%2C%20then%20divide%20by%20three.%3C%2FSPAN%3E%26nbsp%3BMultiply%20this%20by%20the%20volume%20for%20that%20period.%20Record%20the%20result%20in%20a%20spreadsheet%2C%20under%20column%20PV.%3C%2FLI%3E%3CLI%3EDivide%20PV%20by%20the%20volume%20for%20that%20period.%20This%20will%20give%20the%20VWAP%20value.%3C%2FLI%3E%3CLI%3ETo%20maintain%20the%20VWAP%20value%20throughout%20the%20day%2C%20continue%20to%20add%20the%20PV%20value%20from%20each%20period%20to%20the%20prior%20values.%20Divide%20this%20total%20by%20total%20volume%20up%20to%20that%20point.%20To%20make%20this%20easier%20in%20a%20spreadsheet%2C%20create%20columns%20for%20cumulative%20PV%20and%20cumulative%20volume.%20Both%20these%20cumulative%20values%20are%20divided%20by%20each%20other%20to%20produce%20VWAP.%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644964%22%20slang%3D%22en-US%22%3ERe%3A%20%23VALUE!%20ERROR%20WITHOUT%20EXPLAINATIOIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644964%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3B%2C%20formula%20like%3C%2FP%3E%0A%3CPRE%3E%3DSUM(%20(A1%3AC1)%2F3%20)%0A%3C%2FPRE%3E%0A%3CP%3Ereturns%20%23VALUE%20error.%20Use%20SUMPORODUCT%20instead%3C%2FP%3E%0A%3CPRE%3E%3CSPAN%3E%3DSUMPRODUCT(%20(%24AW30%3A%24AY30%2F3)*%24AZ30)%2F%24AZ30%3C%2FSPAN%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Greg Bonaparte
Contributor

The following formal has a #value! error. I havent found the reason: 

 

=SUM((($AW30:$AY30)/3)*($AZ30))/($AZ30)

 

All cells in the formula are at zero when the stock market is closed. I have not yet tested the formula during market open because Im trying to finish before open. Also I want the spread sheet to work when market is closed, even though the data the formula looks at is zero. My spread sheet wont work if this formula returns this error. 

2 Replies

FOLLOW Up:

I should mention that this formula came from these instructions:

  1. Find the average price the stock traded at over the first five-minute period of the day. To do this, add the high, low, and close, then divide by three. Multiply this by the volume for that period. Record the result in a spreadsheet, under column PV.
  2. Divide PV by the volume for that period. This will give the VWAP value.
  3. To maintain the VWAP value throughout the day, continue to add the PV value from each period to the prior values. Divide this total by total volume up to that point. To make this easier in a spreadsheet, create columns for cumulative PV and cumulative volume. Both these cumulative values are divided by each other to produce VWAP.

@Greg Bonaparte , formula like

=SUM( (A1:C1)/3 )

returns #VALUE error. Use SUMPORODUCT instead

=SUMPRODUCT( ($AW30:$AY30/3)*$AZ30)/$AZ30

 

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
36 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies