UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Basic Formula Help, Office 2010    
 
   
keithbrown
post Dec 10 2019, 11:11 AM
Post#1



Posts: 55
Joined: 15-March 12



can anyone tell me how to write this so I don't get an error?

ROUND('Budget Back up'!G23+'Budget Back up'!G24+'Budget Back up'!G25+(('Budget Back up'!G26/'Budget Back up'!H26)*'Budget Back up'!I26)+Budget Back up'!G27+'Budget Back up'!G28+'Budget Back up'!G29+'Budget Back up'!G30+'Budget Back up'!G31+'Budget Back up'!G45+'Budget Back up'!G46+'Budget Back up'!G47+'Budget Back up'!G48+'Budget Back up'!G49,2)

I think the problem is in the middle with this part:
(('Budget Back up'!G26/'Budget Back up'!H26)*'Budget Back up'!I26)

Go to the top of the page
 
cheekybuddha
post Dec 10 2019, 11:19 AM
Post#2


UtterAccess Moderator
Posts: 12,112
Joined: 6-December 03
From: Telegraph Hill


Hi,

You are missing a single quote before Budget Back up'!G27 after the expression you highlighted.

If 'Budget Back up'!H26 might ever be 0 then you can guard against that by using:
CODE
=ROUND('Budget Back up'!G23 + 'Budget Back up'!G24 + 'Budget Back up'!G25 + (IF('Budget Back up'!H26 > 0, ('Budget Back up'!G26/'Budget Back up'!H26), 0) * 'Budget Back up'!I26) + 'Budget Back up'!G27 + 'Budget Back up'!G28 + 'Budget Back up'!G29 + 'Budget Back up'!G30 + 'Budget Back up'!G31 + 'Budget Back up'!G45 + 'Budget Back up'!G46 + 'Budget Back up'!G47 + 'Budget Back up'!G48 + 'Budget Back up'!G49,2)


Or, more legibly:
CODE
=ROUND(
  'Budget Back up'!G23 +
  'Budget Back up'!G24 +
  'Budget Back up'!G25 +
  (IF('Budget Back up'!H26 > 0, ('Budget Back up'!G26/'Budget Back up'!H26), 0) * 'Budget Back up'!I26) +
  'Budget Back up'!G27 +
  'Budget Back up'!G28 +
  'Budget Back up'!G29 +
  'Budget Back up'!G30 +
  'Budget Back up'!G31 +
  'Budget Back up'!G45 +
  'Budget Back up'!G46 +
  'Budget Back up'!G47 +
  'Budget Back up'!G48 +
  'Budget Back up'!G49,2
)


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
kfield7
post Dec 10 2019, 04:59 PM
Post#3



Posts: 1,032
Joined: 12-November 03
From: Iowa Lot


What I see is:

round(sum(g23:g25,IF('Budget Back up'!H26 > 0,g26/h26,0),g27:g31,g45:g49),2)

or if the formula is not on 'Budget Back up' then

round(sum('Budget Back up'!g23:g25,(IF('Budget Back up'!H26 > 0,'Budget Back up'!g26/'Budget Back up'!h26,0),'Budget Back up'!g27:g31,'Budget Back up'!g45:g49),2)
This post has been edited by kfield7: Dec 10 2019, 05:04 PM
Go to the top of the page
 
cheekybuddha
post Dec 10 2019, 05:37 PM
Post#4


UtterAccess Moderator
Posts: 12,112
Joined: 6-December 03
From: Telegraph Hill


@kfield7

Looks like your second expression may have an extra opening bracket:
CODE
round(sum('Budget Back up'!g23:g25,(IF('Budget Back up'!H26 > 0,'Budget Back up'!g26/'Budget Back up'!h26,0),'Budget Back up'!g27:g31,'Budget Back up'!g45:g49),2)
                                   ^
                                   |

--------------------


Regards,

David Marten
Go to the top of the page
 
kfield7
post Dec 11 2019, 07:58 AM
Post#5



Posts: 1,032
Joined: 12-November 03
From: Iowa Lot


Thanks, David
Go to the top of the page
 
cheekybuddha
post Dec 11 2019, 09:52 AM
Post#6


UtterAccess Moderator
Posts: 12,112
Joined: 6-December 03
From: Telegraph Hill


NP! thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th January 2020 - 06:25 PM