Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-17-2015, 07:13 AM
lynchbro lynchbro is offline IF OR Statement for Adjusted Book & Market Values Windows 7 64bit IF OR Statement for Adjusted Book & Market Values Office 2010 32bit
Advanced Beginner
IF OR Statement for Adjusted Book & Market Values
 
Join Date: Jun 2014
Location: New York
Posts: 41
lynchbro is on a distinguished road
Default IF OR Statement for Adjusted Book & Market Values


I am using an IF OR statement to create an adjusted book value of a holdings report. Due to system output of the original holdings, there are rounding in the market value & unrealized gain loss calculations. I will need to use excel to create an adjusted holdings but using the unrounded versions for cost, market value & unrealized.

My formula is:
=IF(OR(N2=0,P2=0),I2,I2+O2-Q2)

N2 is new purchase quantity
P2 is new sale quantity
I2 is original book value
O2 is new purchase book value
Q2 is new sale book value

The way the formula is written above it is only calculating correctly if there is both new purchase & sale quantity >0. I need it to be one or both >0.

Then the same goes for the adjusted market value column:
=IF(OR(N2=0,P2=0),J2,H2*N2-H2*P2+J2)

N2 is new purchase quantity
P2 is new sale quantity
J2 is original market value
H2 is current day price

Again the way the formula is written above it is only calculating correctly if there is both new purchase & sale quantity >0. I need it to be one or both >0.

If needed I can attach an example sheet to better illustrate.
Reply With Quote
  #2  
Old 04-17-2015, 09:08 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline IF OR Statement for Adjusted Book & Market Values Windows 7 64bit IF OR Statement for Adjusted Book & Market Values Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Do you mean something like
Code:
=IF(and(N2=0,P2=0),I2,I2+O2-Q2)
?
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 04-17-2015, 09:14 AM
lynchbro lynchbro is offline IF OR Statement for Adjusted Book & Market Values Windows 7 64bit IF OR Statement for Adjusted Book & Market Values Office 2010 32bit
Advanced Beginner
IF OR Statement for Adjusted Book & Market Values
 
Join Date: Jun 2014
Location: New York
Posts: 41
lynchbro is on a distinguished road
Default

No because there most instances will have N=0 & P=VALUE or vice versa.
Reply With Quote
  #4  
Old 04-17-2015, 09:24 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline IF OR Statement for Adjusted Book & Market Values Windows 7 64bit IF OR Statement for Adjusted Book & Market Values Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

I don't understand what you are trying to do
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 04-20-2015, 10:58 AM
lynchbro lynchbro is offline IF OR Statement for Adjusted Book & Market Values Windows 7 64bit IF OR Statement for Adjusted Book & Market Values Office 2010 32bit
Advanced Beginner
IF OR Statement for Adjusted Book & Market Values
 
Join Date: Jun 2014
Location: New York
Posts: 41
lynchbro is on a distinguished road
Default

Hi Pecoflyer. Thank you for trying to help. I found a work around that didn't require the formulas.
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Where Can I Download Free Go To Market Plan PPT? Davidsmith2 PowerPoint 2 09-27-2015 09:50 PM
Add a statement sma Word 6 03-23-2014 10:30 AM
Page layout can't be adjusted geaker Word 0 10-12-2010 07:46 AM
IF OR Statement for Adjusted Book & Market Values if statement piper7971 PowerPoint 1 08-19-2010 07:10 AM
IF OR Statement for Adjusted Book & Market Values Help with IF statement! CPelkey Word 1 04-12-2010 09:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:44 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft