Help with finishing these formulas please!

Copper Contributor
Hi I’ve been trying to finish my medicine chart on my excel 16.39 version.

I have:

Total batch weight
I don’t know what formula to use for this.

Single portion weight (g)
A2/C2

Single portion potency (mg)
C5*D2

CBD batch potency (mg)
C6/E2

And then I need them to give me:

Total concentrate weight

And

CBD oil to use

But I can’t figure out these two last things. And also when I put in the A2/C2 and the C6/E2 It gave me A warning sign saying “there are one or more circular references where formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly“

And I’m not sure what that means

Any help is much appreciated!
16 Replies

@Shanae1180 Would be helpful if you could show what exactly you have in each of the cells. A circular reference is cause be a formula that, directly references it self. For instance, when you enter in A2, =A2 , that's a circular reference. The same when you enter =B2 in cell A2 where B2 contains the formula =A2

Now, these are very obvious examples, but in real life circular references can sometimes be a bit more difficult to spot and correct. But when you click on the Error Checking icon on the Formula ribbon, you can get some clues as to where to start.

Screenshot 2022-01-20 at 06.51.17.png

C0B275F8-C993-4320-8361-728DF734710D.jpeg

07ED6F7C-EEE8-49DB-80ED-610EC99B4110.jpeg

Hi @riny_van_Eakelen ! Thank you so much for your reply! I’ve taken a bit to get back to you because I’ve been trying to figure out how to send photos to you to show you what I mean. I hope these help 

5F3A5A84-CDCD-4C09-80E5-44A0AAA2CE4F.jpeg

E0ED2D69-AC6D-48DB-BFA5-D0FA3846670A.jpeg

5371FE5C-5448-44D2-8C70-BF6B18C8053F.jpeg

5AAC73B0-EE27-4653-A2E4-8D4AFF36BA80.jpeg

@Shanae1180 Not sure what you have in mind but all formulae on row reference to cell in rich they reside. That's causing circular references as explained in my previous post.

Sorry if this is a silly question, but I’m not very good at this stuff, is that something that I need to change because it will mess up my equation or it is okay?

I’m trying to get the outcome of how much oil to use. I would like my formulas to do the math for me.

@Shanae1180 As you have noticed, circular references don't work. So yes, it messes up things. Can you give an example with real numbers?

 

For instance, number of portions = 45 in C6 and the total batch weighs 1000 in A2, then the formula for weight per portion in C2 would be =A2/C6, returning a value of 22,22222.

 

Now I don't know how you would calculate potion and batch potency is in this context. But I guess, you know. Enter the formulae as the one above, but do NOT include the reference of the cell where you write the formula. Attached a file based on your example. 

Thank you for going through that with me. Can you look at this example that she gave me and see if what you suggested will work?

 

5B925330-1A63-4E37-BF7F-B54C8F23144B.png

3CBF6900-04A2-45E0-9825-CEA746C93537.png

@Shanae1180 What example?

Sorry did the photos come through?
I’ll try resending

@Shanae1180 See attached!

Yes that’s it! How do I get it to work like that?

@Shanae1180 What do you mean? Look in the file I attached and see what's in every cell. Do the same in your own file, though beware that the cell references may be different in yours.

But all I can see are the results, I don’t see the formulas. I want to be able to adjust the information and get different dosages.

Like she’s doing in the beginning of this link

https://youtu.be/pjhIbq7Vy3c

But for cbd

 

All I see in what you sent me is this... 

AF0D5266-60B7-4B98-AC25-3F00A94E8DBE.png

Sorry if I’m not getting it. I’m trying to

@Shanae1180 Click on a cell and look in the formula bar. That's where you can see the formula. In the picture below I clicked on C5. Same principle for all the other cells with something in it. And it works the same on a mobile device, though the screen looks different.

Screenshot 2022-01-21 105401.png

Ohhhh okay that makes sense! Omg that is so helpful wow thank you so much!! I’ve been using my phone so maybe that’s why I couldn’t see it. I will use my laptop when I get to work in the morning. Thank you so much for all of your help!! I really appreciate it.