Divide a formula by itself, inside the same formula?

daytona12345

New Member
Joined
Aug 31, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I'm not even sure what to call this -- I tried googling a couple of ways of phrasing and got nothing.
Essentially what I want to do is reduce the number of separate formulas I have and consolidate to as few as possible (1 is desired).
Right now I have something like:

A1A2
28,174,668,481,289 =A1*2^32/600/1000000000000

So if I leave this as is, I'll get : 201,682,132.84

But what I want to do is add my next calculation into the formula inside A2, which would look like this:

AB
28,174,668,481,289 =A1*2^32/600/1000000000000
28500=A2 / B1

the value in B2 would = 0.0001413114766


What I am trying to do is divide the result that B1 would provide...in the same formula, is that possible?
So like: =(A1*2^32/600/1000000000000) / (A2 / result of B1 cell formula)

Something like this wont work, because the order of operations:
=(A2) / (A1*2^32/600/1000000000000)
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
I figured it out.... Just needed to use parenthesis to force a change in order of operations :eek:

=28500 / (28174668481289 * 2^32/600/1000000000000) * 919 * 23900
 
Upvote 0
I figured it out.... Just needed to use parenthesis to force a change in order of operations :eek:

=28500 / (28174668481289 * 2^32/600/1000000000000) * 919 * 23900
Well that didn't last long, I ran into a problem with the rest of it...I have an IF statement that's not working:

=IF('Financial Projections'!C7=TRUE,B52,B53 / (C14 * 2^32/600/1000000000000) * C16 * E16)

In the example where I thought I figured it out...the number at the front 28500 is one possible result of the if statement. It's based on if I am overclocking or not. 'Financial Projections'!C7' is a checkbox in google sheets where, if checked it is true, and it knows to pull the number B52, if False then it needs to pull the number b53.

What I thought would happen would be, let's say b52 = 28500 -- then I would get the same result as if I ran =28500 / (28174668481289 * 2^32/600/1000000000000) * 919 * 23900

But I do not !
 
Upvote 0
This does not work either.
If false-- it gives me the correct answer, if true it just returns the value in the vlookup cell without doing the calculation inside the vlookup cell
anyone know why? (yes there is a formula in both vlookup cells, and one works one doesnt !


=IF(B40=True,VLOOKUP(H14,B2:C6,2,FALSE) * 'Financial Projections'!C4 * (1+0.25), VLOOKUP(H14,B2:C6,2,TRUE) * 'Financial Projections'!C4 / (C14 * 2^32/600/1000000000000) * C16 * E16 * ('Financial Projections'!C9-'Financial Projections'!B9))
 
Last edited:
Upvote 0
=IF('Financial Projections'!C7=TRUE,B52,B53 / (C14 * 2^32/600/1000000000000) * C16 * E16)
You have a bracket in the wrong place. Try:

=IF('Financial Projections'!C7,B52,B53) / (C14 * 2^32/600/1000000000000) * C16 * E16

if true it just returns the value in the vlookup cell without doing the calculation inside the vlookup cell

=IF(B40=True,VLOOKUP(H14,B2:C6,2,FALSE) * 'Financial Projections'!C4 * (1+0.25), VLOOKUP(H14,B2:C6,2,TRUE) * 'Financial Projections'!C4 / (C14 * 2^32/600/1000000000000) * C16 * E16 * ('Financial Projections'!C9-'Financial Projections'!B9))
If B40 is True, your formula will return the value of: VLOOKUP(H14,B2:C6,2,FALSE)*'Financial Projections'!C4*1.25

What do you want it to return?
 
Upvote 0
Solution
You have a bracket in the wrong place. Try:

=IF('Financial Projections'!C7,B52,B53) / (C14 * 2^32/600/1000000000000) * C16 * E16


If B40 is True, your formula will return the value of: VLOOKUP(H14,B2:C6,2,FALSE)*'Financial Projections'!C4*1.25

What do you want it to return?
huh...so I didn't even need to write TRUE, or "TRUE" or TRUE....

=IF('Financial Projections'!C7,B52,B53) / (C14 * 2^32/600/1000000000000) * C16 * E16

This works for me.
for whatever reason what I had -- it would give me the answer if True, but if false it returned the cell value BEFORE doin the formula in the cell. I guess I just didn't realize you didnt need to actually write the word true
 
Upvote 0
huh...so I didn't even need to write TRUE, or "TRUE" or TRUE....

If A1 contains the value TRUE or FALSE (we call this a Boolean value - there are only two possibilities) then

=IF(A1=TRUE,"It's true","It's false") can be written more succinctly as =IF(A1,"It's true","It's false")

The text value "TRUE" is not the same as the boolean TRUE, so if you were using "True" in A1, you'd need to say: =IF(A1="TRUE","It's true","It's false")

Did you get your second problem resolved?
 
Upvote 0
If A1 contains the value TRUE or FALSE (we call this a Boolean value - there are only two possibilities) then

=IF(A1=TRUE,"It's true","It's false") can be written more succinctly as =IF(A1,"It's true","It's false")

The text value "TRUE" is not the same as the boolean TRUE, so if you were using "True" in A1, you'd need to say: =IF(A1="TRUE","It's true","It's false")

Did you get your second problem resolved?
yes all good ! ty !
Yeah I had been trying several ways, with TRUE and then also another cell to capture the value of the checkbox and man, simple solution!

Thanks for the help
 
Upvote 0
You could also shorten your formula. You have this bit whether B40 is TRUE or FALSE:

VLOOKUP(H14,B2:C6,2,FALSE)*'Financial Projections'!C4

So you can take it outside the IF(), rather than having it in there twice:

=VLOOKUP(H14,B2:C6,2,FALSE)*'Financial Projections'!C4*IF(B40,1.25, C1*E16*('Financial Projections'!C9-'Financial Projections'!B9)/(C14*2^32/600/1000000000000))
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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