how to reference a variable value (not the best of descriptions)

Post Reply
User avatar
Princy557
Posts: 192
Joined: Wed May 18, 2011 10:32 am

how to reference a variable value (not the best of descriptions)

Post by Princy557 »

Hi
I have a couple of worksheets one sheet creates a set of values and a graph. Contains, date, calculated values, reading from meters etc.
A second worksheet has a date column and a tariff column. The value of the tariff can vary on a daily basis and is entered as required by the user. The entry is added each time so there is a historic value available.
How do I do a calculation on worksheet 1 using the 'latest' values on worksheet 2?
Regards

Princy557
SuperTech
SoftMaker Team
SoftMaker Team
Posts: 2935
Joined: Wed Mar 11, 2020 5:31 pm

Re: how to reference a variable value (not the best of descriptions)

Post by SuperTech »

It actually depends on what calculations you want to do. Sharing complete details with sample files will be more helpful in this case.
User avatar
Princy557
Posts: 192
Joined: Wed May 18, 2011 10:32 am

Re: how to reference a variable value (not the best of descriptions)

Post by Princy557 »

Hi,
Well basically the only calculation detail would be a tariff, then the other cells would work of the results of that.
I have solar panels and get a feed-in tariff from the gas company. I just want to be able to estimate to a closer level how much will be paid to me after the readings are taken. Reading are done weekly but payment every 90 days.
It's not essential that I have this working, but it's been an annoyance for some time that I don't know how I would do it.
I'm struggling to keep my eyes open, will get more detail in the morning.
Regards

Princy557
User avatar
Princy557
Posts: 192
Joined: Wed May 18, 2011 10:32 am

Re: how to reference a variable value (not the best of descriptions)

Post by Princy557 »

Hi
Currently, I actually add the information to the tariff page and note it. Then make amendments to the formulae's every time I do a calculation or FIT entry. (FIT- Feed-In Tariff)
It would all become a much neater page if I could just reference the tariff page, either the entry by date or by the last entry.

Just thoughts,
Any input would help my knowledge
Regards

Princy557
SuperTech
SoftMaker Team
SoftMaker Team
Posts: 2935
Joined: Wed Mar 11, 2020 5:31 pm

Re: how to reference a variable value (not the best of descriptions)

Post by SuperTech »

At least please share the formula before and after modification.
User avatar
Princy557
Posts: 192
Joined: Wed May 18, 2011 10:32 am

Re: how to reference a variable value (not the best of descriptions)

Post by Princy557 »

Sorry,
Snap_Saturday, 8 January 2022_16h29m47s_002_FIT calculations.pmd - PlanMaker.jpg
The amount due is calculate with E47 =(D47*(17.87/100))+((D47/2)*(5.38/100))

Values are taken from tariff worksheet and hand-entered
26/07/2019 £0.1787 £0.0538 £0.2325
15/01/2020 £0.1787 £0.0538 £0.2325 <- this in example above
01/07/2020 £0.1826 £0.0550 £0.2376
04/10/2021 £0.1848 £0.0557 £0.2405

Hope that makes sense and there is now enough information given
Regards

Princy557
SuperTech
SoftMaker Team
SoftMaker Team
Posts: 2935
Joined: Wed Mar 11, 2020 5:31 pm

Re: how to reference a variable value (not the best of descriptions)

Post by SuperTech »

If I understand your situation correctly, you are getting one data on one file with "£" sign which you have to type in another file in number format for calculation manually.

Here is the stepwise solution for you:
1. Copy the source data cell from the source file.
2. In the target file use paste special > Paste reference.
3. You will get the reference of the source cell, on the bases of which you can type the final formula like this one which I wrote for you with my sample files.
4. After writing the formula for one cell, you can simply drag the formula for the other cells of the column as per your requirements.

For cell E1

Code: Select all

=(D1*SUBSTITUTE('[Source file.pmdx]Sheet1'!B1,"£",""))+((D1/2)*(SUBSTITUTE('[Source file.pmdx]Sheet1'!B1,"£","")))
Please let me know if you have any confusion.

Sample files are attached here:
Source file.pmdx
(5.98 KiB) Downloaded 142 times
Final file.pmdx
(6.44 KiB) Downloaded 152 times
User avatar
Princy557
Posts: 192
Joined: Wed May 18, 2011 10:32 am

Re: how to reference a variable value (not the best of descriptions)

Post by Princy557 »

Hi SuperTech,
Thanks for taking the time.
I've just ditched about fifty worksheets which were all messing around by me NOT getting anywhere. So we are left with

'Graph' where the formulae are and 'Tariff' where the incremental data is updated. Maybe this is a clearer way of showing what I'm trying to do.

Tariff is where I add various changes as prices happen. What I think I need to do is to (in formula ways) point at the last entry to get the combined Generation + collection values Coumnsl B and C, or use the single value in column D.
'Graph' needs to use B & C or D as the value in the formulae.

Is that a better explanation?

Attached are the worksheets

P.S. I'm working through your formula, struggling a bit, but on it. I have a Best Before Date of about 2015. It's been downhill since then. ha ha
Attachments
FIT TEST calculations.pmd
(32.5 KiB) Downloaded 136 times
Regards

Princy557
SuperTech
SoftMaker Team
SoftMaker Team
Posts: 2935
Joined: Wed Mar 11, 2020 5:31 pm

Re: how to reference a variable value (not the best of descriptions)

Post by SuperTech »

Thanks for sharing the file and details. I have added the formula for you and for better performance, I have made some changes in your file.

Tariff is limited to 500 rows, which you can increase as per your requirements later.
FIT TEST calculations.pmd
(36.5 KiB) Downloaded 125 times
User avatar
Princy557
Posts: 192
Joined: Wed May 18, 2011 10:32 am

Re: how to reference a variable value (not the best of descriptions)

Post by Princy557 »

Hi SuperTech
Thank you so much, I'll spend some time this afternoon and see if I can learn how you've done it.
But it certainly looks good.
:D :D :D :D :D :D :D :D
Regards

Princy557
User avatar
Princy557
Posts: 192
Joined: Wed May 18, 2011 10:32 am

Re: how to reference a variable value (not the best of descriptions)

Post by Princy557 »

Hi SuperTech,
I think I've noticed a problem.
When the formulae are used the next time, the tariff has changed, all the values above change to the newest tariff

Tariff
01/07/2020 £0.1826 £0.0550 £0.2376
04/10/2021 £0.1848 £0.0557 £0.2405
added tariff
18/01/2022 £0.1878 £0.0559

Graph should be:
16/01/2022 16105 18 £3.83 **
18/01/2022 16108 3 £0.64

but comes out changing the previous value
16/01/2022 16105 18 £3.88 **
18/01/2022 16108 3 £0.65

Is there a way of fixing in stone, all previous values?

My first thought is that the Tariff value will have to be copied to the Graph sheet or the Tariff sheet is superfluous and the tariff would become part of the Graph sheet.

Any thoughts?
(other than flipping bloke!)
Regards

Princy557
SuperTech
SoftMaker Team
SoftMaker Team
Posts: 2935
Joined: Wed Mar 11, 2020 5:31 pm

Re: how to reference a variable value (not the best of descriptions)

Post by SuperTech »

I have just added the formula so that you can get the latest tariff values in graph sheet as per your requirements. No other change.

Here are the values I am getting after adding your latest tariff values.
Updated tariff.JPG
User avatar
Princy557
Posts: 192
Joined: Wed May 18, 2011 10:32 am

Re: how to reference a variable value (not the best of descriptions)

Post by Princy557 »

HI,
Was there supposed to be an attachment?
Regards

Princy557
SuperTech
SoftMaker Team
SoftMaker Team
Posts: 2935
Joined: Wed Mar 11, 2020 5:31 pm

Re: how to reference a variable value (not the best of descriptions)

Post by SuperTech »

Do you want attachment also? I used the same file which I shared last time and just entered one new entry in tariff. No other change.
Post Reply

Return to “PlanMaker 2021 for Windows”