two formulas..are they the same

w123kid

New Member
Joined
May 9, 2008
Messages
42
Hi i have been working on a spreadsheet not originally designed by me

i have the following formula

=SUM((ThroughputFormulas!C11:C14)*(K2/30)/1024*ThroughputFormulas!C38*M2)
+(SUM(ThroughputFormulas!C33:C36)*Q2*(K2/30)/1024*ThroughputFormulas!C38*M2)



and this




=SUM(ThroughputFormulas!C11:C14)+[FONT=&quot](SUM(ThroughputFormulas!C33:C36)*Q2)*(K2/30))/1024*M2[/FONT]



the difference between the two is that it will not multiply by ThroughputFormulas!C38 but i cannot get the second one to work (originally part of a longer formula)



Eliminating the ThroughputFormulas!C38 from the first formula will it give me same results in both... second one is probably an easier set up and would like to replace it but i cannot get it to work




help much appric:cool:?
 
That was missing a parenthesis:
Code:
=IF(ISERROR(((SUM(ThroughputFormulas!C11:C14)+(SUM(ThroughputFormulas!C33:C36)*Q2))*(K2/30)/1024*M2*3600*O2)/IF(Q2=60,SUM(ThroughputFormulas!C39:C45)/1024/8,1))," ",SUM((ThroughputFormulas!C11:C14)+(SUM(ThroughputFormulas!C33:C36)*Q2))*(K2/30)/1024*M2*3600*O2/IF(Q2=60,SUM(ThroughputFormulas!C39:C45)/1024/8,1))
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
That was missing a parenthesis:
Code:
=IF(ISERROR(((SUM(ThroughputFormulas!C11:C14)+(SUM(ThroughputFormulas!C33:C36)*Q2))*(K2/30)/1024*M2*3600*O2)/IF(Q2=60,SUM(ThroughputFormulas!C39:C45)/1024/8,1))," ",SUM((ThroughputFormulas!C11:C14)+(SUM(ThroughputFormulas!C33:C36)*Q2))*(K2/30)/1024*M2*3600*O2/IF(Q2=60,SUM(ThroughputFormulas!C39:C45)/1024/8,1))

managing to validate it using cntrl shift enter - previous to that i was getting #value error but number is till not accuarate :(

more work needed
 
Upvote 0
Code:
=IF(ISERROR(((SUM(ThroughputFormulas!C11:C14)+(SUM(ThroughputFormulas!C33:C36)*Q2))*(K2/30)/1024*M2)/IF(Q2<70,SUM(ThroughputFormulas!C42:C45),1 )*3600/1024/8*O2)," ",SUM((ThroughputFormulas!C11:C14)+(SUM(ThroughputFormulas!C33:C36)*Q2))*(K2/30)/1024*M2/IF(Q2=60,SUM(ThroughputFormulas!C39:C45),1))*3600/1024/8*O2

hows this look?
 
Upvote 0
I can't really say unless you tell me what it is supposed to be doing! I have no idea what cells it is supposed to be looking at, or what the order of precedence in the calculation should be.
 
Upvote 0
=IF(ISERROR(((SUM(ThroughputFormulas!C11:C14)+(SUM(ThroughputFormulas!C33:C36)*Q2))*(K2/30)/1024*M2)/IF(Q2<70,SUM(ThroughputFormulas!C42:C45),1 )*3600/1024/8*O2)," ",SUM((ThroughputFormulas!C11:C14)+(SUM(ThroughputFormulas!C33:C36)*Q2))*(K2/30)/1024*M2/IF(Q2=60,SUM(ThroughputFormulas!C39:C45),1))*3600/1024/8*O2

</pre>


teh above formula is used to determine storage :(Data rate calculation / percentage factor if less than 70 )*3600(hour)/ 1600/8 (Mb to GB)* number of data streams
 
Upvote 0
Rich (BB code):
=IF(ISERROR(((SUM(ThroughputFormulas!C11:C14)+(SUM(ThroughputFormulas!C33:C36)*Q2))*(K2/30)/1024*M2)/IF(Q2<70,SUM(ThroughputFormulas!C42:C45),1 )*3600/1024/8*O2)," ",SUM((ThroughputFormulas!C11:C14)+(SUM(ThroughputFormulas!C33:C36)*Q2))*(K2/30)/1024*M2/IF(Q2=60,SUM(ThroughputFormulas!C39:C45),1))*3600/1024/8*O2


teh above formula is used to determine storage
Data rate calculation
/ percentage factor if less than 70 )*3600(hour)/ 1024/8 (Mb to GB)* number of data streams
 
Upvote 0
OK, and what exactly is the problem?
 
Upvote 0
i am not getting the exact calulation

as i was before...steps on calculations might be off ....some multiplicationa re prob getting done on different numbers if u know what i mean..

edit:
actually i think where the prob is ...should [FONT=&quot]*3600)*O2)) as my original formula before dividing by IF statement as this is prob giviong me huge numbers after

will give this a go shortly
[/FONT]
 
Last edited:
Upvote 0
Not really. Is this what you need:
Code:
=IF(ISERROR(((SUM(ThroughputFormulas!C11:C14)+(SUM(ThroughputFormulas!C33:C36)*Q2))*(K2/30)/1024*M2)/IF(Q2<70,SUM(ThroughputFormulas!C42:C45),1 )*3600/1024/8*O2)," ",((SUM(ThroughputFormulas!C11:C14)+(SUM(ThroughputFormulas!C33:C36)*Q2))*(K2/30)/1024*M2)/IF(Q2<70,SUM(ThroughputFormulas!C42:C45),1 )*3600/1024/8*O2)
 
Upvote 0
this was my original formula
Code:
=IF(ISERROR(((((((SUM(ThroughputFormulas!C105:C108)*(100%-Q4)+(SUM(ThroughputFormulas!C127:C130)*Q4)*(K4/30))/1000)*M4)*3600)*O4)/(SUM(ThroughputFormulas!C136:C139))/1024/8)),"",((((((SUM(ThroughputFormulas!C105:C108)*(100%-Q4)+(SUM(ThroughputFormulas!C127:C130)*Q4)*(K4/30))/1000)*M4)*3600)*O4)/(SUM(ThroughputFormulas!C136:C139))/1024/8))
With this code above getting soem settings as an exampel i get 1.3 GB
with yours i get 36GB??

Where am i going wrong
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top