Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
139
I have inherited an Excel Spreadsheet (Excel 2002) at work which contains the following formula (it contains 381 characters). The calculations are mostly to do with money. Some of the answers I get are correct. Some are a penny out and others are several pence out. I have managed to work out that where a cell is being divided by the cell at D5 the answer needs to be rounded to two decimal places before being timesed by 7 (this is because I am trying to replicate a process that was previously done manually. TIA


=IF(M5="","",IF(C5>0,IF((E5>M5)*AND(M5>F5),M5-F5,IF((M5>E5)*AND(M5>F5),G5,IF((F5>E5)*AND(E5>M5),0,IF((M5>F5)*AND(F5>E5),E5-F5,IF((F5>M5)*AND(M5>E5),E5-M5,IF(M5="","",0)))))),IF((E5>R5)*AND(R5>F5),M5-((F5/D5)*7),IF((R5>E5)*AND(E5>F5),((E5-F5)/D5)*7,IF((R5>F5)*AND(F5>E5),((E5-F5)/D5)*7,IF((F5>E5)*AND(E5>R5),0,IF((F5>(M5/7)*D5)*AND(M5>(E5/D5)*7),((E5/D5)*7)-M5,IF(M5="","",0))))))))

</SPAN>

TIA
Chris​
 

LxQ

Well-known Member
Joined
Feb 9, 2006
Messages
619
I got lost trying to follow the logic... but try this: any time you have something like
((E5-F5)/D5)*7
use
ROUND((E5-F5)/D5,2)*7

also, instead of:
IF((E5>M5)*AND(M5>F5),

use:
IF(AND(E5>M5,M5>F5),

or just:
IF((E5>M5)*(M5>F5),
 

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
139
I got lost trying to follow the logic... but try this: any time you have something like
((E5-F5)/D5)*7
use
ROUND((E5-F5)/D5,2)*7

also, instead of:
IF((E5>M5)*AND(M5>F5),

use:
IF(AND(E5>M5,M5>F5),

or just:
IF((E5>M5)*(M5>F5),
Thanks for the help. I have used your round part of the formula plus the If(andE5>M5,M5>F5 option, which appears to have worked. The only problem now is trying to replicate the answers that I have at work from a different system.

It is a bit worrying as most of the figures agree, but there are about 5 that differ by 1 to 5 pence. Perhaps, the other system does not calculate the same way that Excel does.

Thanks once again for your help it is very much appreciated.
 

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
139
In the second part of the formula below, is it possible to roundup all the parts of the formula where it is doing a divide and times? I have tried several variations and all without success. Please see bold type below.


IF((E5>R5)*AND(R5>F5),M5-((F5/D5)*7),IF((R5>E5)*AND(E5>F5),((E5-F5)/D5)*7,IF((R5>F5)*AND(F5>E5),((E5-F5)/D5)*7,IF((F5>E5)*AND(E5>R5),0,IF((F5>(M5/7)*D5)*AND(M5>(E5/D5)*7),((E5/D5)*7)-M5,IF(M5="","",0))))))))
 

miss_ell

Active Member
Joined
Jun 19, 2002
Messages
274
Hello,

Just a suggestion but instead of =ROUND((E5-F5)/D5,2)*7 why not try =ROUND(((E5-F5)/D5)*7,2)? The might help to reduce the difference in pennies.
 

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
139
Thanks for posting your earlier idea. I will give it a go. I have just found out the other system in question has been rounding the figures incorrectly i.e. it has been rounding up in all instances.
 

Forum statistics

Threads
1,081,690
Messages
5,360,614
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top