if the prices keep changing, how do i add the different prices for different days is there any other way to fix this?

A

acarwreck

New Member
Joined
Mar 13, 2024
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
PRICEPRICEDate:12345
APPLE4525QTY1246
ORANGE3035QTY11
BRICK100125QTY2
MOON150140QTY472
FERRARI25500QTY35
DAY TOTAL8451951801080695
 
Sort by date Sort by votes
Peter_SSs said:
Could you use something like this?

Make a table like I have in columns BA:BC. This table records the date that each item changes price and what that ne price is. This column could even be on a different worksheet.
Notice that the values in the bright blue cells in row 13 are actually dates but I have formatted them to just show the day.
The formulas in row19 look up the latest price as at the date in row 13 and multiply by the quantities in rows 14:18

24 04 02.xlsm
EFGHIJKLMAZBABBBC
1ItemDatePrice
2APPLE1/01/202450
3ORANGE1/01/202430
4POTATOES1/01/202454
5SPINACH1/01/202432
6EGGPLANT1/01/202412
7SPINACH2/01/202433
8APPLE3/01/202455
9APPLE5/01/202457
10
11
12
13ITEMDATE :12345
14APPLEQTY 5551
15ORANGEQTY 3231
16POTATOESQTY 41
17SPINACHQTY 5241
18EGGPLANTQTY 521
19DAY TOTAL2934306431275186
20
Changing price
Cell Formulas
RangeFormula
H19:L19H19=LET(BA,$BA2:$BA1000,BB,$BB2:$BB1000,SUMPRODUCT(H14:H18,VLOOKUP($E14:$E18,FILTER($BA2:$BC1000,BB=MAXIFS(BB,BB,"<="&H13,BA,BA)),3,0)))
Click to expand...
Hey thanks for the response, i will try this and see if it works for me.
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Peter_SSs said:
Could you use something like this?

Make a table like I have in columns BA:BC. This table records the date that each item changes price and what that ne price is. This column could even be on a different worksheet.
Notice that the values in the bright blue cells in row 13 are actually dates but I have formatted them to just show the day.
The formulas in row19 look up the latest price as at the date in row 13 and multiply by the quantities in rows 14:18

24 04 02.xlsm
EFGHIJKLMAZBABBBC
1ItemDatePrice
2APPLE1/01/202450
3ORANGE1/01/202430
4POTATOES1/01/202454
5SPINACH1/01/202432
6EGGPLANT1/01/202412
7SPINACH2/01/202433
8APPLE3/01/202455
9APPLE5/01/202457
10
11
12
13ITEMDATE :12345
14APPLEQTY 5551
15ORANGEQTY 3231
16POTATOESQTY 41
17SPINACHQTY 5241
18EGGPLANTQTY 521
19DAY TOTAL2934306431275186
20
Changing price
Cell Formulas
RangeFormula
H19:L19H19=LET(BA,$BA2:$BA1000,BB,$BB2:$BB1000,SUMPRODUCT(H14:H18,VLOOKUP($E14:$E18,FILTER($BA2:$BC1000,BB=MAXIFS(BB,BB,"<="&H13,BA,BA)),3,0)))
Click to expand...
it says that the function is invalid. i copy pasted the same thing you posted.
 
Upvote 0
Can you confirm that you are using Microsoft 365?

Perhaps you are using different regional settings/version? What happens if you try this formula
=LET(BA;$BA2:$BA1000;BB;$BB2:$BB1000;SUMPRODUCT(H14:H18;VLOOKUP($E14:$E18;FILTER($BA2:$BC1000;BB=MAXIFS(BB;BB;"<="&H13;BA;BA));3;0)))
 
Upvote 0
Peter_SSs said:
Can you confirm that you are using Microsoft 365?

Perhaps you are using different regional settings/version? What happens if you try this formula
=LET(BA;$BA2:$BA1000;BB;$BB2:$BB1000;SUMPRODUCT(H14:H18;VLOOKUP($E14:$E18;FILTER($BA2:$BC1000;BB=MAXIFS(BB;BB;"<="&H13;BA;BA));3;0)))
Click to expand...
i am using microsoft 365, i had a feeling i might be using an older version so i updated it yesterday and still it didnt work, i will use this formula and see if it works
 
Upvote 0
Peter_SSs said:
Can you confirm that you are using Microsoft 365?

Perhaps you are using different regional settings/version? What happens if you try this formula
=LET(BA;$BA2:$BA1000;BB;$BB2:$BB1000;SUMPRODUCT(H14:H18;VLOOKUP($E14:$E18;FILTER($BA2:$BC1000;BB=MAXIFS(BB;BB;"<="&H13;BA;BA));3;0)))
Click to expand...
it says something is wrong with the formula
 
Upvote 0
acarwreck said:
it says something is wrong with the formula
Click to expand...
Is that what it said for the post 10 formula too?

Try this
  1. Start a blank worksheet
  2. In post #10, click this icon in the top left of my Mini Sheet:
    1712234785930.png
  3. Select cell E1 in the new blank worksheet
  4. Paste
What happens then?
 
Upvote 0
Peter_SSs said:
Is that what it said for the post 10 formula too?

Try this
  1. Start a blank worksheet
  2. In post #10, click this icon in the top left of my Mini Sheet: View attachment 109449
  3. Select cell E1 in the new blank worksheet
  4. Paste
What happens then?
Click to expand...
yes i did that earlier, it gave me an error saying that "That function isn't valid", i think i have posted a screenshot in one of my replies. And it was highlighting the table_array in the Vlookup function, in which you have put the filter function.
 
Upvote 0
acarwreck said:
yes i did that earlier, it gave me an error saying that "That function isn't valid", i think i have posted a screenshot in one of my replies. And it was highlighting the Filter function.
Click to expand...

Peter_SSs said:
Is that what it said for the post 10 formula too?

Try this
  1. Start a blank worksheet
  2. In post #10, click this icon in the top left of my Mini Sheet: View attachment 109449
  3. Select cell E1 in the new blank worksheet
  4. Paste
What happens then?
Click to expand...
i did some googling and found out that if i get the filterxml, while typing filter, it means im using an older an older version of excel. But i have updated microsoft 365 to the latest version and i just checked again, it says that its up to date. Just FYI im using the home and student 2019 version
 
Upvote 0
acarwreck said:
But i have updated microsoft 365 to the latest version and i just checked again, it says that its up to date. Just FYI im using the home and student 2019 version
Click to expand...
Those two red comment are contradictory. You are either using Microsoft 365 or you are using Excel 2019. They are different products.

The final red statement above also contradicts your earlier assertion..
acarwreck said:
i am using microsoft 365
Click to expand...
It also contradicts your user profile
1712305815468.png


What are you actually using? If you click File -> Account this is what I get.

1712305613462.png


What do you get?
 
Upvote 0
You must log in or register to reply here.

Similar threads

etaf
  • Question Question
Calculate Sales price to achive orginal price required after fees
Replies
8
Views
299
etaf
etaf
Data123
  • Question Question
Excel 365 ETF Prices (=B2.Price)
Replies
4
Views
205
Data123
Data123
B
  • Question Question
Generate formula to calculate sell price with respect to buy price, buy& sell out fees and desired profit margin percentage (changing variable)
Replies
1
Views
402
Cubist
Cubist
H
  • Question Question
List out duplicate items in a list with different costs/values
Replies
6
Views
188
H3MSY
H
O
  • Question Question
Cannot trap error when using Excel's built-in stock price functionality in a macro. It used to work well!
Replies
1
Views
251
OaklandJim
O

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

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